Detecting <CR><LF> and SubString data in between

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

Detecting <CR><LF> and SubString data in between

Author

Topic

jorame22
Starting Member

1 Post

Posted - 2012-07-30 : 13:47:56

Hi all,

I have a field which contains quite a few <CR><LF>. I need to detect them and SubString what's between each one. I included some data example below. I need to be able to get what's between each <CR><LF>.What will be the best approach for this? Any help will be appreciate it.

000010008382143000010000081193 700691007530 0000000001T000004592 0000000001<CR><LF>
000020008396400000010000081193 700691010769 0000000003T000001928 0000000003<CR><LF>
000030008393509000030000081193 700691019823 0000000001T000000558 0000000001<CR><LF>
000040008396400000030000081193 700691010783 0000000004T000000814 0000000004<CR><LF>
000050008396400000050000081193 700691010806 0000000001T000004269 0000000001<CR><LF>

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-30 : 13:54:12

You can use a string splitter function such as described in Fig. 21 of Jeff Moden's article here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

After you install that function, you would use it like this:

DECLARE @x VARCHAR(8000) = 'abcd'+CHAR(13)+CHAR(10) + 'efgh'+CHAR(13)+CHAR(10) + '1234';
SELECT
	ItemNumber,
	Item
FROM
	MASTER.dbo.DelimitedSplit8K(REPLACE(@x,CHAR(13)+CHAR(10),'|'),'|');

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources