Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 replace field value using wildcard
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Posting Yak Master

210 Posts

Posted - 02/23/2014 :  12:50:59  Show Profile  Reply with Quote
Is it possible to 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 and remove it from the string. 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.

Let's say my field value is this:

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

I want the result to be this:

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

I was trying several variations:

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

Any help is appreciated.

Yak Posting Veteran

75 Posts

Posted - 02/23/2014 :  18:41:15  Show Profile  Reply with Quote
Wildcard can use LIKE clause or some function.
Like following.

UPDATE myTable
SET myField = LEFT(myField, PATINDEX('%;A0%', myField))
WHERE myField LIKE '%;A0%'

From Japan
Sorry, my English ability is limited.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000