Avatar of Ben Conner
Ben Conner
Flag for United States of America asked on

Specifying a non-printable character constant in T-SQL

Hi,

I need to specify some non-printable characters as a search argument in a REPLACE function in T-SQL (MS SQL .  I thought this would be easy to find.  It isn't.

Is there a way to do this?

Thanks!

--Ben
Microsoft SQL Server 2005SQL

Avatar of undefined
Last Comment
Ben Conner

8/22/2022 - Mon
Raja Jegan R

>> 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..
Ben Conner

ASKER
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
ASKER CERTIFIED SOLUTION
Rajkumar Gs

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Rajkumar Gs

Try these also.

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

Open in new window

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Rajkumar Gs

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;

Open in new window

Rajkumar Gs


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

Open in new window

SOLUTION
js-profi

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Ben Conner

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

--Ben
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.