[Resolved] How to find <cr> in a field

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

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

[Resolved] How to find <cr> in a field

Author

Topic

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-24 : 13:29:52

Hi all,

I have a table, with a field type char(60) (and many more fields as well)

That field is having some strange data, which drives me nuts.

When I query the the table, in Query Analyzer it looks no different with the other data, when the results are shown in Grid. But when the results are shown as Text, some records are seperated by a blank line. I suspect that as a Carriage Return in the data

Am I correct or is there any other issue
How to find such records (if <CR> is the case) ?

Here is a typical query & the O/P I get


Select [description] from p where F in( '2291', '64601', '007008','6122', '0235', '0223')


The results as


description                                                  
------------------------------------------------------------ 
AAAAAAAAAAAAAAAAAA                                          
BBBBBBBBBBBBBBBBBB                                          
CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
                  
DDDDDDDDDDDDDDD                                                 
EEEEEEEEEE                                         
FFFFFFFFFFFFFFFFFFF                                         
GGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG
                         
HHHHHHHHHHHHHHHHHHHHHHHHHHH
                           

(8 row(s) affected)

Any suggestions are welcome
Thanks

Edit:
I tried
SELECT * FROM p where CHARINDEX([F],CHAR(13)+CHAR(10)) > 0
But no records returned

-- OK I finally get hold of the guy
With the help of
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55855


SELECT *
FROM p where patindex('%'+CHAR(13)+CHAR(10)+'%',f) > 0

Thanks
Srinika

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-24 : 13:43:38

CR or LF? CHAR(13) or CHAR(10)

SELECT * FROM MyTable WHERE CHARINDEX(CHAR(13), MyField) > 0 OR CHARINDEX(CHAR(10), MyField) > 0

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources