Menu
I need to replace multiple characters in a string. The result can't contain any '&' or any commas.
I currently have:
REPLACE(T2.[ShipToCode],'&','and') But how do you put multiple values in?
Many thanks!
473
If you wanted to replace the words with blank string, go with REGEXP_REPLACE() . If you want to replace the words with other words, for example replacing & with and then use replace() . If there are multiple words to be replaced, use multiple nested replace() .
Using the REPLACE() function will allow you to change a single character or multiple values within a string, whether working to SELECT or UPDATE data.
If you want to replace multiple characters you can call the String. prototype. replace() with the replacement argument being a function that gets called for each match. All you need is an object representing the character mapping that you will use in that function.
In a view, you could do it like: select case when col1 like '00%' then stuff(col1, 1, 2, '11') else col1 end from YourTable; Live example at SQL Fiddle. Just a note, the substring should be "substring(col1, 3, len(col1)-2)"because you want to start at 3rd character and the characters are numbered from 1, not 0.
You just need to daisy-chain them:
REPLACE(REPLACE(T2.[ShipToCode], '&', 'and'), ',', '') One comment mentions "dozens of replace calls"... if removing dozens of single characters, you could also use Translate and a single Replace.
REPLACE(TRANSLATE(T2.[ShipToCode], '[];'',$@', '#######'), '#', '') If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
