I have a varchar field which will contain a long numeric identifier, similar to a telephone number. I don't mind if it is stored with embedded separators, like
When extracting the data, I want to eliminate the separators, so my stored proc should return the same data as:
The obvious solution would be
str_replace( my_column, '-', '' )
But wait: the documentation says: "The empty string (“ ”) is treated as a single space.". In consequence, the replace command produces a string which has one blank instead of each of the dashes. result:
123 456 7890
In other situations, the str_replace() function seems to have no problem producing strings which are longer or shorter than the original. Only the empty string causes problems.
Did anyone figure out a way to replace a sequence of characters by an empty string ?
I don't know in advance how many blanks my string will contain. I could write a loop, using charindex, then taking the portion after and before - yukk