asked on

replace field value using wildcard

How can I replace a string value that uses a wildcard? For example, I'm trying to update a field on a table and replace the field value where it starts with a specific two character pattern followed by any series of characters and just replace it with just a two character pattern. Something like this:

UPDATE myTable SET myField = REPLACE(myField,'XX%','XX')

But, the above doesn't work?

Any help is appreciated.
Thanks.

Kent Olsen

You're close. :)

UPDATE mytable SET myfield = 'something'
WHERE myfield like 'XX%';

or

UPDATE mytable SET myfield = 'something'
WHERE substring (myfield, 1, 2) = 'XX';

They're equivalent.

Good Luck,
Kent

-Dman100-

ASKER

Hi Kent,

Thanks for replying to my post. My apologies, my explanation was not correct. Here is an example of what I'm trying to do. Let's say my field value is this:

;00236;08231;06106 Washington DC;06106 CCA Wash DC;05120;A0106;

Open in new window

I want the result to be this:

;00236;08231;06106 Washington DC;06106 CCA Wash DC;05120;

Open in new window

The field is a delimited string and I want to replace any occurrence of ;A0% or ;A0* with a null value. I don't want to replace the entire field with a null value.

Does that help explain?

Thanks.

ASKER CERTIFIED SOLUTION

Kent Olsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

If you're running 2012, the REPLACE command is trivial.
As far as I know REPLACE (Transact-SQL) has not changed since its introduction with SQL Server 7.

Is there something I am missing?

Kent Olsen

Hi Anthony,

I thought that REPLACE came in with 2012. Or am I completely off in space???

Anthony Perkins

If you check the link I posted you will see that it includes documentation for 2005 (the oldest supported version), however I believe that function was first introduced with SQL Server 7 (If I recall correctly it did not exist in SQL Server 6.5)

Kent Olsen

Too many databases and too many versions to keep in my head. :(

Apologies, Dman. As Anthony points out, it may well work with the version that you have.

Kent