Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

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.
0
-Dman100-
Asked:
-Dman100-
  • 4
  • 2
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
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
0
 
-Dman100-Author Commented:
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.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Dman,

The short answer is, "it depends on what version of SQL Server you're using".

There's a long-hand method that will work on any version of SQL Server, but it's a bit cumbersome and ugly.  If you're running 2012, the REPLACE command is trivial.

Let's hope that you're using 2012.  :)

UPDATE mytable SET myfield = REPLACE (myfield, oldvalue, newvalue)
WHERE myfield like '%oldvalue%';


Kent
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Anthony PerkinsCommented:
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?
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Anthony,

I thought that REPLACE came in with 2012.  Or am I completely off in space???
0
 
Anthony PerkinsCommented:
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)
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now