Finding Newline Characters With 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.

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.

Ben Nadel's Gravatar I have never seen PATINDEX before. Looks interesting. I would traditionally do something like this:

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
Entrance exam's Gravatar thanks for useful SQL query
# Posted By Entrance exam | 9/28/10 2:20 AM
Custom Essays's Gravatar Thanks for sharing, I just like the valuable info you provide for your articles. I’ll bookmark your weblog and take a look at once more right here regularly.
# Posted By Custom Essays | 6/28/17 11:20 AM
BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.