How to add split between duplicate value in SQL

January 25, 2022

I have SQL Server table that have duplicated value in field and need to split by slash character. All field start with alphabetical char end with ‘M’ char

For example:

     X123X123M
     Y1515Y1515M

and need to convert to:

     X123/X123M
     Y1515/Y1515M

Thanks

>Solution :

Probably many ways to skin this cat, assuming you’re always looking for the same initial character repeated within the string, the following uses stuff to insert a slash at the appropriate location:

with t as (
    select 'X123X123M' val union all
    select 'Y1515Y1515M'
)
select val, Stuff(val,v,0,'/')
from t
cross apply(values(CharIndex(Left(val,1),val,2)))v(v)