Pull string between a certain string and a tab (char(9))

Question

Pull string between a certain string and a tab (char(9))

Jeff Sims-413169

Say Hey Kid

Points: 711
More actions
February 2, 2016 at 8:45 am

#399662

I need to pull whatever the string is in a text column that occurs after the word Requestor: and before the next tab or char(9) in the text field. I am getting frustrated trying to accomplish the stop at the tab. This is a free form field in our form so there could be any format of string and number of words after Requestor:

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

You just need a combination of SUBSTRING and CHARINDEX. Be sure to consider the possibility of strings missing Requestor: or char(9) or both.

If you want help with the code, please post sample data and what you've tried.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ScottPletcher SSC Guru Points: 101099 More actions · Answer 2

Here's some sample code, in case it helps:

DECLARE @text_column varchar(max)

SET @text_column =

'I need to pull whatever the string is in a text column that occurs after the word Requestor: and before the next tab or ' + char(9) + ' in the text field. I am getting frustrated trying to accomplish the stop at the tab. This is a free form field in our form so there could be any format of string and number of words after Requestor:' + CHAR(9)

SELECT

text_column,

CASE WHEN requestor_byte = 0 THEN '<Not found>'

ELSE SUBSTRING(text_column, requestor_byte + literal_length, tab_byte - requestor_byte - literal_length)

END AS requestor_value

FROM (

SELECT @text_column AS text_column UNION ALL

SELECT 'let''s see what happens if no tab is found after Requestor: but there''s still a bunch of text after it.'

) AS test_data

CROSS APPLY (

SELECT LEN('Requestor:') AS literal_length,

CHARINDEX('Requestor:', text_column) AS requestor_byte

) AS assign_alias_names1

CROSS APPLY (

SELECT CASE WHEN CHARINDEX(CHAR(9), text_column, requestor_byte) = 0

THEN LEN(text_column) + 1 ELSE CHARINDEX(CHAR(9), text_column, requestor_byte) END AS tab_byte

) AS assign_alias_names2

Edit: Adjusted code so that if no char(9) found, it will use the rest of the text as "Requstor:" data.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Sims-413169 Say Hey Kid Points: 711 More actions · Answer 3

Thanks. I was very close, actually changed to that code as I didn't allow if there was no tab after. Plus I basically had it right in my code, sorry didn't post it in first post except I had to CAST the text column as a varchar(max) to work with the LEN -1, etc.

Thanks.

Jeff Sims-413169 Say Hey Kid Points: 711 More actions · Answer 4

Thanks. I basically had it just wasn't casting the text field as varchar(max) so was having issues with the LEN -1. But also ended up using your code with a few changes as I wasn't accounting for no tab after the first string. thanks.