Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 373
  • 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-Software ConsultantAuthor 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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