replace field value using wildcard

Posted on 2014-02-22
Last Modified: 2014-02-25
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.
Question by:-Dman100-
  • 4
  • 2
LVL 45

Expert Comment

ID: 39879893
You're close.  :)

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


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

They're equivalent.

Good Luck,

Author Comment

ID: 39880817
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?

LVL 45

Accepted Solution

Kdo earned 500 total points
ID: 39880836
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%';

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

LVL 75

Expert Comment

by:Anthony Perkins
ID: 39883402
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?
LVL 45

Expert Comment

ID: 39883478
Hi Anthony,

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

Expert Comment

by:Anthony Perkins
ID: 39884379
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)
LVL 45

Expert Comment

ID: 39884410
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.


Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL profiler equivalent in MS-Access 3 44
SQL Exceptions 3 39
Counting connections to SQL Server through C# 3 29
Update SQL to SP1 on SCCM server 7 16
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now