SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

-Dman100-
Posting Yak Master

209 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.
Thanks

nagino
Yak Posting Veteran

Japan
55 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  
 New 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.03 seconds. Powered By: Snitz Forums 2000