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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hommer
Aged Yak Warrior

808 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
938 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

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