sql-server - Using Substring to pull out a fixed number of characters starting a given number in a separate column in SQL Server Management Studio

Question

I have a database of webchats that has a single string field of the complete chat transcript (including visitor time, text, advisor's responses and times etc) that I want to pull data from.

I have previously used some Substring code to pull out 8 characters, starting from the 10th character in order to get me the timestamp attached to when a visitor starting talking to the advisor;

SubString([chat transcript], 10, 8) as [visitor message time]

I have also used CharIndex to pull from the field to tell me the character number at which the Advisor is first mentioned (aka, when they first reply).

CharIndex([chat agent alias],[chat transcript],0) as [alias location in chat]

This doesn't tell me when the advisor first replied however. In the transcript the advisor's first reply is followed by a timestamp, and what I want to be able to do is pull the timestamp for each different advisor.

I was wondering it's possible to do something similar with the Substring function as above, but instead of stating the fixed Character number it should start from, it instead uses the number in the Alias Location In Chat field instead. (see table below, of which I want it to for row 4 for example, search for the 138th character in the Chat Transcript field, and pull out the next eight characters in that field)

Something like:

SubString([chat transcript], [alias location in chat], 8) as [visitor message time]

Does anyone have an idea of how to do this?

标签： sql-serversubstringssms

Answer 1

I would not do that in bare T-SQL as it is pathetic when it comes to string manipulation. For example it doesn't implement regular expressions which I think would be of tremendous help here. Given this task I would probably use Python to connect to this database using pyodbc and then fetch and process the textual data using wealth of Python libraries available.