Can you stop extracting text after first CR? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Can you stop extracting text after first CR?

Author

Topic

bobV
Starting Member

8 Posts

Posted - 2012-09-25 : 09:57:03

Is there a way to stop pulling in text from a query after a CR is detected?

EX Stuff.Cell:
This is a test.
one
two
three

I would like to just extract "This is a test."

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-25 : 10:14:08

The query below removes anything after CR or LF. If you want to look for only CR, remove the LF and it should work the same way.

DECLARE @x VARCHAR(32) = 'abcd'+CHAR(10)+CHAR(13)+'efgh';
SELECT 
	CASE 
		WHEN @x LIKE '%['+CHAR(10)+CHAR(13)+']%' THEN
			STUFF (@x,PATINDEX('%['+CHAR(10)+CHAR(13)+']%',@x),LEN(@x),'')
		ELSE @x
	END;

bobV
Starting Member

8 Posts

Posted - 2012-09-25 : 11:00:59

Thanks Sunitabeck.

I understand the concept but am a little tripped up with implementing it.

I have a table (td.test) with a list of test names (ts_description) and am trying to remove the text after the first CR in ts_description. I tried to modify your example but am getting a syntax error. Here's the SQL, any help would be greatly appreciated!

SELECT
CASE
WHEN ts_description LIKE '%['+CHAR(10)+']%' THEN
td.test ( ts_description,PATINDEX('%['+CHAR(10)+']%', ts_description),LEN( ts_description),'')
ELSE ts_description
END;
FROM td.TEST

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-25 : 11:08:25

You have a semi-colon before the "FROM" keyword. Semi-colon is end-of-statement indicator. Also, I think the td.test is not required - you need the STUFF keyword

SELECT CASE 
            WHEN ts_description LIKE '%[' + CHAR(10) + ']%' THEN STUFF (
                     ts_description,
                     PATINDEX('%[' + CHAR(10) + ']%', ts_description),
                     LEN(ts_description),
                     ''
                 )
            ELSE ts_description
       END
FROM td.TEST

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources