Finding Newline Characters With SQL

Posted At : September 20, 2007 1:38 PM | Posted By : Nathan Mische
Related Categories: SQL

I ran into something today that I've never had to do before: I needed to select rows from a SQL Server database table where a column had a newline character (CR+LF). After a little searching I found one approach.

SELECT * 

FROM Table 

WHERE PATINDEX('%'+CHAR(13)+CHAR(10)+'%',Column) > 0

I hadn't used SQL Server's PATINDEX function before, but it looks like it could be pretty useful. If anyone has another approach please let me know.

Comments (5) | Print | Send | del.icio.us | Digg It! | Linking Blogs | 2418 Views
Comments
[Add Comment]
Ben Nadel's Gravatar I have never seen PATINDEX before. Looks interesting. I would traditionally do something like this:

WHERE
Column LIKE '_%#Chr( 13 )##Chr( 10 )%'

The "_" requires at least one character before the first line return.
# Posted By Ben Nadel | 9/20/07 2:08 PM
Nathan Mische's Gravatar I guess I should have mentioned I was running this query in Query Analyzer, not via ColdFusion. Nice tip on the underscore though!
# Posted By Nathan Mische | 9/20/07 3:03 PM
Sana's Gravatar PATINDEX is really powerfull function mssql. Anway thanks for such a great tip.

@Ben [LIKE] is far more slow than PATINDEX.
# Posted By Sana | 9/21/07 4:48 AM
Ben Nadel's Gravatar @Sana,

Thanks for the tip. Good to know that kind of stuff - still learning over here :)
# Posted By Ben Nadel | 9/21/07 7:12 AM
Ben Nadel's Gravatar @Nathan,

Sorry, I misunderstood your post the first time I read it. I didn't realize that PATINDEX() returned a one-based result. Therefore, when you had a "> 0", I thought that meant AFTER the first characters. I now know that you were checking for any existence. Ooops :) My original comment made no sense.
# Posted By Ben Nadel | 9/24/07 8:14 AM
[Add Comment]
BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.