Specifying a non-printable character constant in T-SQL

>> I need to specify some non-printable characters as a search argument in a REPLACE function

Kindly explain more on this with examples to help you better..

Hi,

One of the columns in a table has embedded cr/lf combinations  I need to replace those with blanks.

I thought the following would work:

select Replace(description,'0d0a'x,'  ') as desc
from products

where description is a varchar field.  I can't use the literal hex codes in this case as they get interpreted as a new line. :(  So...  I've tried x'0d0a', '0d0a'x, h'0d0a', '0d0a'h but can't come up with another way to write a hex specification for a 2 character search string. ??

Thanks!

--Ben

Try these also.

--Raj

SELECT dbo.regexReplace(yourfield, '[\x00-\x1f]', '', 1, 1 ) FROM yourtable;

Select allOpen in new window

Not sure. Just give a try

--Raj

DECLARE @regex integer;
SET @regex = dbo.regexObj( '[^a-z]', 1, 1 );
SELECT dbo.regexObjReplace( @regex, firstname, '' ) FROM account;

Select allOpen in new window

--Raj

DECLARE @regex integer;
SET @regex = dbo.regexObj( '[\x00-\x1f]', 1, 1 );
SELECT dbo.regexObjReplace( @regex, firstname, '' ) FROM account;

Select allOpen in new window

Man, I love this group! :)  Thanks very much!

--Ben