SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Can you stop extracting text after first CR?		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bobV
Starting Member

8 Posts
Posted - 09/25/2012 :  09:57:03  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts
Posted - 09/25/2012 :  10:14:08  Show Profile  Reply with Quote
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;
Go to Top of Page

bobV
Starting Member

8 Posts
Posted - 09/25/2012 :  11:00:59  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts
Posted - 09/25/2012 :  11:08:25  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000