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
 SQL Server 2000 Forums
 SQL Server Development (2000)
 How to find a carriage return in text field?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hommer
Aged Yak Warrior

766 Posts

Posted - 09/29/2005 :  16:20:13  Show Profile  Reply with Quote
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

USA
933 Posts

Posted - 09/29/2005 :  16:22:20  Show Profile  Reply with Quote
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

Edited by - nathans on 09/29/2005 16:25:44
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 09/29/2005 :  16:22:36  Show Profile  Visit ditch's Homepage  Reply with Quote
CHAR(13)

Duane.
Go to Top of Page

nosepicker
Constraint Violating Yak Guru

USA
366 Posts

Posted - 09/29/2005 :  16:25:28  Show Profile  Reply with Quote
Check for CHAR(10) also, which is a line feed.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/29/2005 :  16:25:28  Show Profile  Reply with Quote
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

Edited by - X002548 on 09/29/2005 16:26:02
Go to Top of Page

Hommer
Aged Yak Warrior

766 Posts

Posted - 09/29/2005 :  16:43:18  Show Profile  Reply with Quote
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)
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 09/29/2005 :  17:56:05  Show Profile  Reply with Quote
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
Go to Top of Page

Fromper
Starting Member

USA
30 Posts

Posted - 12/01/2009 :  12:20:33  Show Profile  Reply with Quote
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

Edited by - Fromper on 12/01/2009 12:22:43
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 12/01/2009 :  12:48:03  Show Profile  Reply with Quote
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



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.09 seconds. Powered By: Snitz Forums 2000