SSMS 2008/SQL - how to extract specific string of text

Question

Struggling with this one.

I have a table called requeststext, the 2 columns of interest here are;

'Title' (nvarchar(255), not null)
'Description' (ntext, null)

I would like to update multiple rows, setting each row's 'Title' to be specific text from their 'Description' field.

Example;

Title is always;

New incident has been raised

Description is a very large string of data and it also contains line breaks, an example extract would be;

How it appears in SSMS*;

This Request has been raised:<CRLF>==================================================[CRLF]Request 123456: My PC doesn't work - please help![CRLF]==================================================[CRLF]Type: Incident[CRLF]...

How it appears in the application (easier to read);

This Request has been raised: 
==================================================
Request 123456: My PC doesn't work - please help!
==================================================
Type: Incident
...

Things to note about description;

* I added in the [CRLF]'s as these don't appear in SSMS
The text/characters within the field remain in the same positions, the only varying part is the 'My PC doesn't work - please help!' part, this could be varying in text and length.

The desired result would be Title being changed to:

"My PC doesn't work - please help!"

I have been trying to get PATINDEX to work with SUBSTRING but I just can't seem to get it to work.

score 0 · Answer 1 · edited May 23 '17 at 12:30

0

You need a split function so that you can slice the data by delimiter and then get the desired output, here's a good example How to split string using delimiter char using T-SQL?

edited May 23 '17 at 12:30

Community

1
1

answered Nov 29 '16 at 11:08

singhswat

832
7
20

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

Try it like this

DECLARE @tbl TABLE ([Description] VARCHAR(MAX));
INSERT INTO @tbl VALUES 
(
'This Request has been raised:
==================================================
Request 123456: My PC doesn''t work - please help!
==================================================
Type: Incident'
);

SELECT SUBSTRING(NoLineBreaks.s,posFirst,CHARINDEX('=',NoLineBreaks.s,posFirst)-posFirst)
FROM @tbl AS tbl
CROSS APPLY(SELECT REPLACE(REPLACE([Description],CHAR(13),''),CHAR(10),'')) AS NoLineBreaks(s)
CROSS APPLY(SELECT CHARINDEX(': ',NoLineBreaks.s)+2) AS FirstColon(posFirst);

The first CROSS APPLY throws away CHAR(13) and CHAR(10) to get rid of line breaks. The second finds the first colon with a following blank.

Finally you use SUBSTRING and start at the found position until the first =.

UPDATE: Use this for a table's update

You edited your question with some details (please avoid chameleon questions!). The issue was to update the Titel with the extracted text for multiple columns.

For this I'd suggest an updateable CTE:

Attention The declared table variable @tbl is just a mock-up to create a test scenario. You do not need this!! Just replace @tbl within the CTE with your actual table's name. Carefull with real data!!

DECLARE @tbl TABLE (Title VARCHAR(100),[Description] VARCHAR(MAX));
INSERT INTO @tbl VALUES 
('New incident has been raised'
,'This Request has been raised:
==================================================
Request 123456: My PC doesn''t work - please help!
==================================================
Type: Incident'
)
,('New incident has been raised'
,'This Request has been raised:
==================================================
Request 123456: Some other text
==================================================
Type: Incident'
);

--This is the table's content before the update
SELECT * FROM @tbl;

--The CTE returns two columns: One is the Title, the other one the new content

WITH UpdateableCTE AS
(
    SELECT SUBSTRING(NoLineBreaks.s,posFirst,CHARINDEX('=',NoLineBreaks.s,posFirst)-posFirst) AS NewTitle
          ,tbl.Title
    FROM @tbl AS tbl
    CROSS APPLY(SELECT REPLACE(REPLACE([Description],CHAR(13),''),CHAR(10),'')) AS NoLineBreaks(s)
    CROSS APPLY(SELECT CHARINDEX(': ',NoLineBreaks.s)+2) AS FirstColon(posFirst)
)
UPDATE UpdateableCTE SET Title=NewTitle;

--The new content after the update
SELECT * FROM @tbl;

This is getting to where I want to go but I was hoping to just do it without declaring? Mainly because I don't ever use it so I'm not sure what consequences it will have - what happens to local variables once you're done with them, are they automatically deleted? How do I incorporate the above into an update statement? `update requesttext set title = ( your whole statement?)` The 'values' is a field within a column called description - there could be numerous rows, would this work for numerous rows? — , Nov 29 '16 at 13:49
I've updated my question to further clarify there are numerous rows here. I would like to select directly from the table rather than select from a declared variable table - is this possible at all? — , Nov 29 '16 at 14:14

score 0 · Answer 3 · answered Nov 29 '16 at 18:00

Another way:

-- your sample data
DECLARE @tbl TABLE (Title VARCHAR(100),[Description] VARCHAR(MAX));
INSERT INTO @tbl VALUES 
('New incident has been raised'
,'This Request has been raised:
==================================================
Request 123456: My PC doesn''t work - please help!
==================================================
Type: Incident'
)
,('New incident has been raised'
,'This Request has been raised:
==================================================
Request 123456: Some other text
==================================================
Type: Incident'
);

-- alternative solution:
WITH 
s1 AS 
(
  SELECT title, string = SUBSTRING([Description], CHARINDEX('=', [Description])+50, 1000)
  FROM @tbl
),
s2 AS 
(
  SELECT title, string, start = CHARINDEX(':', string)
  FROM s1
)
SELECT 
  Title, 
  [Description] = SUBSTRING(string, start+1, CHARINDEX('=', string, start)-(start+1))
FROM s2;

SSMS 2008/SQL - how to extract specific string of text

3 Answers3

UPDATE: Use this for a table's update