| Author |
 Topic  |
|
|
Hommer
Aged Yak Warrior
808 Posts |
 Posted - 09/29/2005 : 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
USA
938 Posts |
Posted - 09/29/2005 : 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 |
Edited by - nathans on 09/29/2005 16:25:44 |
 |
|
|
ditch
Flowing Fount of Yak Knowledge
South Africa
1466 Posts |
Posted - 09/29/2005 : 16:22:36
|
CHAR(13)
Duane.  |
|
|
|
nosepicker
Constraint Violating Yak Guru
USA
366 Posts |
Posted - 09/29/2005 : 16:25:28
|
Check for CHAR(10) also, which is a line feed.
|
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 09/29/2005 : 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
|
Edited by - X002548 on 09/29/2005 16:26:02 |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 09/29/2005 : 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)
USA
7020 Posts |
Posted - 09/29/2005 : 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
USA
33 Posts |
Posted - 12/01/2009 : 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 |
Edited by - Fromper on 12/01/2009 12:22:43 |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 12/01/2009 : 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
|
|
|
| |
Topic |
|
How to find a carriage return in text field?