Menu
I am trying to replace the nth character in SQL Server. I tried with it using replace():
SELECT REPLACE(ABC,0,1) FROM XXX In above code all zeros will be replaced with one, but I only want to change it in a particular position and sometimes that position can change.
215
SQL Server REPLACE() FunctionThe REPLACE() function replaces all occurrences of a substring within a string, with a new substring. Note: The search is case-insensitive.
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.
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() .
use stuff The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
select STUFF(ABC, starting_index, 1, 'X') from XXX "Here your int position to replace" is the position no just replace with any int no and that position will be replaced Note : (Thanks to pcnate for suggestion) starting_index is your int position to replace.
190
You're looking for STUFF:
select STUFF(ABC, @n, 1, 'X') from XXX This would replace the @nth character with an X.
Technically it seeks into the original string at column ABC starting at position @n, deletes 1 character, then inserts the string 'X' at that position.
41
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
