How to find a carriage return in text field? - 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

SQL Server 2000 Forums

SQL Server Development (2000)

How to find a carriage return in text field?

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2005-09-29 : 16:20:13

I need to find the position of a carriage return/line feed in a text field. What should I give my PATINDEX('%???%') to look for?
A package software created them. In VBA, they could be identified by vbcrlf. What is the equivalent in T-SQL?

Thanks!

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-09-29 : 16:22:20

CHAR(13) is TSQL equivalent of Carriage Return.
CHAR(10) is LineFeed.
~ CRLF

ex:

SELECT 'This is the first line' + CHAR(13) + 'This is second line'

Nathan Skerl

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-29 : 16:22:36

CHAR(13)

Duane.

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-09-29 : 16:25:28

Check for CHAR(10) also, which is a line feed.

X002548
Not Just a Number

15586 Posts

Posted - 2005-09-29 : 16:25:28

quote:
Originally posted by Hommer

I need to find the position of a carriage return/line feed in a text field.

text field or varchar

SELECT CHARINDEX(Col,CHAR(13)+CHAR(10)) FROM Table

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Hommer
Aged Yak Warrior

808 Posts

Posted - 2005-09-29 : 16:43:18

Awesome!
How can I forget the char(13) coming from a vb world!

Another little twist here, there are more than one of it in the text field. Is there a easy or right way to find, say the third one?

I am thinking replace the first one with *, then find the next...

BTW, the correct syntax to find the first one is:
patindex('%'+CHAR(13)+CHAR(10)+'%',head_conds)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-09-29 : 17:56:05

If you are searching a true text column (datatype TEXT), i don't think this will work for you:

SELECT CHARINDEX(Col,CHAR(13)+CHAR(10))
FROM (select col = convert(text,' ') ) a

Server: Msg 256, Level 16, State 2, Line 1
The data type text is invalid for the charindex function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.

This is OK, though:

SELECT POS = patindex('%'+CHAR(13)+CHAR(10)+'%',Col)
FROM (select col = convert(text,' '+CHAR(13)+CHAR(10)) ) a

POS
-----------
3

CODO ERGO SUM

Fromper
Starting Member

33 Posts

Posted - 2009-12-01 : 12:20:33

I know this thread is 4 years old, but I just wanted to say thanks!!! I don't know why I bother googling my SQL questions - I always find the solutions here on sqlteam.com! I ought to know by now to just come here first.

In case anyone's curious, we save two lines of street addresses in the same field with a carriage return and line feed in between, so I split them out using this (on SQL Server 2008):

select
Street,
case charindex(char(13), Street)
when 0 then Street
else left(Street, charindex(char(13), Street) - 1)
end as Street1,
case charindex(char(13), Street)
when 0 then ''
else substring(Street, charindex(char(13), Street) + 2, 50)
end as Street2
from Address

--Fromper

PS. Well, that looks awkward. Looks like the forum insists on left justifying all my code. So much for my nice, neat formatting. :p

X002548
Not Just a Number

15586 Posts

Posted - 2009-12-01 : 12:48:03

You need to use [ code] ...some code... [ /code] tags

without the leading space

quote:

Originally posted by Fromper


select 
    Street,
    case charindex(char(13), Street)
        when 0 then Street
        else left(Street, charindex(char(13), Street) - 1) 
    end as Street1,
    case charindex(char(13), Street)
        when 0 then ''
        else substring(Street, charindex(char(13), Street) + 2, 50) 
    end as Street2
from Address

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources