Extract string from Text field SQL Server 2012

Question

I have a text field called description that lists a lot of information and I want to extract string between specific words. Text value sample:

General Benefits: The purpose of this is to explain the benefits from using our products.

Health Factors: Please check out our website for a complete list.

Comments: Any comment added by the user is entered here.

Contact Us: Please call us at xxx-xxx-xxxx

General Benefits, Health Factors, Comments, and Contact Us will be always in the text field, but the statement after each one is different. The output should extract the string between these four strings:

Does this post help answer your question? http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x — Daniel, Mar 10 '16 at 23:23
Thanks Daniel. I tried it, but it did not split the string where I want to. — user2536008, Mar 10 '16 at 23:36
I'm not clear about it. Is this the case: you have one field including all text and you want to split it into 4 fields you have in the above table? Please clarify, what exactly is in the original field. — FLICKER, Mar 11 '16 at 01:31
Are your text delimiters always in the same order and terminated by cr/lf as shown in the example? — Liesel, Mar 11 '16 at 02:20
In the original text field, I have text as I entered it, starts from "General Benefits" and ends with line of "Contact Us." Required output is splitting this text field into 4 fields as the image shows. They are in the same order and terminated by crlf. — user2536008, Mar 11 '16 at 07:41

score 1 · Accepted Answer · answered Mar 14 '16 at 06:49

This seems to do what you're after - hope it's of use. I'm sure there are better ways of doing this :)

I've created a test case with two rows in a temp table just for completeness as I assumed you have many rows that you wish to process.

CREATE TABLE #Test (Txt NVARCHAR(MAX))

INSERT INTO #Test (Txt) VALUES
(N'General Benefits: The purpose of this is to explain the benefits from using our products. Health Factors: Please check out our website for a complete list. Comments: Any comment added by the user is entered here. Contact Us: Please call us at xxx-xxx-xxxx'),
(N'General Benefits: Some other benefits. Health Factors: Some other factors. Comments: Love your work. Contact Us: Call us zzz-zzz-zzzz');


DECLARE @PAT1 NVARCHAR(MAX) = N'General Benefits:';
DECLARE @PAT2 NVARCHAR(MAX) = N'Health Factors:';
DECLARE @PAT3 NVARCHAR(MAX) = N'Comments:';
DECLARE @PAT4 NVARCHAR(MAX) = N'Contact Us:';



SELECT 
    SUBSTRING(Txt, A + LEN(@PAT1) + 1, B - A - LEN(@PAT1) - 1) 'General Benefits',
    SUBSTRING(Txt, B + LEN(@PAT2) + 1, C - B - LEN(@PAT2) - 1) 'Health Factors',
    SUBSTRING(Txt, C + LEN(@PAT3) + 1 , D - C - LEN(@PAT3) - 1) 'Comments',
    SUBSTRING(Txt, D + LEN(@PAT4) + 1 , LEN(Txt) - LEN(@PAT4) - 1) 'Contact Us'
FROM #Test 
CROSS APPLY (SELECT 
    A = PATINDEX('%'+@PAT1+'%', Txt), 
    B = PATINDEX('%'+@PAT2+'%',Txt),
    C = PATINDEX('%'+@PAT3+'%',Txt),
    D = PATINDEX('%'+@PAT4+'%',Txt)
) Q


General Benefits                                                                                                                                                                                                                                                 Health Factors                                                                                                                                                                                                                                                   Comments                                                                                                                                                                                                                                                         Contact Us
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The purpose of this is to explain the benefits from using our products.                                                                                                                                                                                          Please check out our website for a complete list.                                                                                                                                                                                                                Any comment added by the user is entered here.                                                                                                                                                                                                                   Please call us at xxx-xxx-xxxx
Some other benefits.                                                                                                                                                                                                                                             Some other factors.                                                                                                                                                                                                                                              Love your work.                                                                                                                                                                                                                                                  Call us zzz-zzz-zzzz

(2 row(s) affected)

Extract string from Text field SQL Server 2012

1 Answers1