I would like to change multiple substrings at once for example '0100001000100' The desired output. ' | ABC | | | | DFG | | | HIG | |'
REPLACE(SUBSTRING([column],1,1),' 1 ' ,' XYZ '),SUBSTRING([column],2,1),' 1 ' ,' zzz ')....
but does not work.
-
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a tag for the database product you are using. Why should I tag my DBMSuser330315– user3303152020-07-29 19:53:23 +00:00Commented Jul 29, 2020 at 19:53
Add a comment
|
1 Answer
It's extremely unclear how you mean your input to turn to your desired output. However, hopefully this will clear up your confusion about the REPLACE function.
REPLACE(targetstring, str1, str2) will find each occurrence of str1 in targetstring and replace it with str2. So REPLACE('I know John and John knows me', 'John', 'Fred') would result in the string 'I know Fred and Fred knows me'.
To chain REPLACEs together, so that the output from the first REPLACE is used as the target for the next replace, you need a structure like this:
REPLACE(
REPLACE(
REPLACE(
'I know Fred and Fred knows me', 'Fred', 'Bill'
), 'know', 'like'
), 'and', 'but'
)
Next point: if you want to string together your replacement results on each character, you need to not chain the REPLACEs but instead join them with +:
REPLACE(SUBSTRING([column],1,1),'1','XYZ')
+
REPLACE(SUBSTRING([column],2,1),'1','zzz')
+ ...
