Searching for multiple carriage returns in a text field

[ColinGregory]

Hi Everyone,

I have a text type field which may contain one or more carriage returns. Is there a way to search the field and only select those records which contain two or more carriage returns?

I've trawled the archive and BOL with no luck. Any pointers much appreciated.

Many thanks
Colin

 

[SonOfEmidec1100]

I think this works....

select * from mytable where patindex('%' + char(13) + '%' + char(13) + '%' ,mytextfield) > 0

 

[collierd]

Only pointers I can give, and you may already know this, are that it will probably involve a patindex of a patindex to return where, if present, the second carriage return occurs
So say where not 0
Also, carriage return is generally either char(13) or '\n'
Have a look at char and patindex

Damian.

 

[ColinGregory]

Totally spot on.

Thanks for the speedy replies lads.

Colin