Identify Hard Return within SQL data

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

Transact-SQL (2000)

Identify Hard Return within SQL data

Author

Topic

Krb_iiaba
Starting Member

16 Posts

Posted - 2004-04-19 : 12:18:17

I have a table with approx 50k records that is used for a magazine circulation. It has come to my attention that a handful of records contain hard returns. What syntax should I use to determine which records are affected so that I can correct the problem.

Obviously this won't work
select * from table where addressline1 like '%chr(10)%'
That will look for literal chr(10)...

Never had to deal with this one before.

Thanks in advance

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-04-19 : 13:35:09

Could this work?

DECLARE @Return char
SET @Return = chr(10)

select * from table where addressline1 like '%' + @Return + '%'

Dustin Michaels

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-19 : 14:43:14

it could....


USE Northwind
GO

SELECT REPLACE(Address,CHAR(10),'*') AS NewAddress 
  FROM Employees WHERE Address Like '%'+CHAR(10)+'%'
GO

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources