catch special characters like {CRLF}

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)

catch special characters like {CRLF}

Author

Topic

hennep
Starting Member

4 Posts

Posted - 2004-08-31 : 11:21:06

I need to do an search on the table one of the three parameters have to match it works fine but the address field which is imported from an access based vb application contains breaks(CrLf).

declare @ln varchar(50),@pc varchar(50),@ad varchar(255)
set @ad = 'nast hyde farm (CrLf) WILKINS GREEN'
select * from customers
where [Last Name]+'%' like @ln Or Postcode like '%'+@pc+'%' Or
Address like '%'+RTRIM(LTRIM(@ad))+'%'

so if I enter set @ad = 'nast hyde farm' no problem I get a match
but if I enter set @ad = ' farm WILKINS' I get no match because of the break(CrLf).

how can I escape them? I can not take them out becuase once the data is processed it has to be send back to the access application offline.

thanx

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-08-31 : 11:24:57

Try the REPLACE statement on your column in the final line. Something like

REPLACE(Address, CHAR(13), '') like '%' + RTRIM(LTRIM(@ad)) + '%'

Raymond

hennep
Starting Member

4 Posts

Posted - 2004-08-31 : 11:34:34

quote:
Originally posted by raymondpeacock

Try the REPLACE statement on your column in the final line. Something like

REPLACE(Address, CHAR(13), '') like '%' + RTRIM(LTRIM(@ad)) + '%'

Raymond

sorry doesnot work
if I start the search before or after the break it works but I still can't include it and it doesn't want to be replaced

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-31 : 12:45:14

crlf is actually char(13) + char(10)

so try: REPLACE(Address, CHAR(13) + CHAR(10), '') like '%' + RTRIM(LTRIM(@ad)) + '%'

Corey

hennep
Starting Member

4 Posts

Posted - 2004-09-01 : 04:58:08

quote:
Originally posted by Seventhnight

crlf is actually char(13) + char(10)

so try: REPLACE(Address, CHAR(13) + CHAR(10), '') like '%' + RTRIM(LTRIM(@ad)) + '%'

Corey

desparation is starting to kick in I am working against a tight death line and I have not written a line of code in anger :), thanx for all your ideas sofar it still doesn't work but any suggestion is welcome

jules

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-01 : 07:31:44

Can you provide some sample data.... specifically the kind you are having a problem with? Then I could actually try my suggestions before I send them back to you...

Corey

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-09-01 : 07:36:37

I suspect we jumped forward too far...is the (CRLF) actually visible in the input data?
if so...

select * from customers
where [Last Name] like '%'+@ln+'%' Or Postcode like '%'+@pc+'%' Or
replace(Address,'(crlf)','') like '%'+RTRIM(LTRIM(@ad))+'%'

should work....experiment with getting rid of the '(crlf)' stuff out of the address field....and then proceed to getting the real query to work.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources