Skip to content
Advertisement

SQL REPLACE value based on position in string

I have a column where the values are all in this format ‘###-##-#-##-##-###-##-#’ I need to format all of the values to where the 2nd to last hyphen is replaced with a period ‘###-##-#-##-##-###.##-#’

Can I use REPLACE based on the position in the string?

Example would be changing ‘068-27-0-40-12-012-00-0’ to ‘068-27-0-40-12-012.00-0’

Answer

Assuming you are in sql server, use the STUFF method to get your work done.

SELECT STUFF(‘###-##-#-##-##-###-##-#’, 19, 1, ‘.’);

First parameter – character expression
Second parameter – starting from nth position
Third parameter – no of positions to be replaced
Fourth parameter – string to be replaced