SQL Server: Return a string in a specific format

In TSQL, I need to format a string in a predefined format. For eg:

SNO	STRING	FORMAT	OUTPUT
1	A5233GFCOP	*XXXXX-XXXXX	*A5233-GFCOP
2	K92374	/X-000XXXXX	/K-00092374
3	H91543987	XXXXXXXXX	H91543987

I am trying with FORMATMESSAGE() built in function.

For ex:

1. FORMATMESSAGE(‘*%s-%s’,’A5233′,’GFCOP’)
2. FORMATMESSAGE(‘/%s-000%s’,’K’,’92374′)
3. FORMATMESSAGE(‘%s’,’H91543987′)

I am able to get the first argument by replace function but issue is second/third/fourth/.. arguments. I don’t know how to count respective X’s between the various delimiters, so that I can use substring to pass in second/third/.. arguments. If I can count the respective # of X’s from the Format column, I feel using substring we can get it but not sure how to count the respective X’s.

Please let me know how to get through it or if there is any other simple approach.

Appreciate your help.

Thanks!

Answer

It’s in theory quite simple, could probably be done set-based using string_split however that’s not ideal as the ordering is not guaranteed. As the strings are fairly short then a scalar function should suffice. I don’t think it can use function in-lining.

The logic is very simple, create a counter for each string, loop 1 character at a time and pull a character from one or the other into the output depending on if the format string is an X or not.

create or alter function dbo.fnFormatString(@string varchar(20), @format varchar(20))
returns varchar(20)
as
begin
    declare @scount int=1, @fcount int=1, @slen int=len(@string), @flen int=Len(@format), @output varchar(20)=''
    while @scount<=@slen or @fcount<=@slen
    begin
        if Substring(@format,@fcount,1)='X'
        begin 
            set @output+=Substring(@string,@scount,1)
            select @scount+=1, @fcount +=1
        end
        else
        begin
            set @output+=Substring(@format,@fcount,1)
            set @fcount +=1
        end
    end
    return @output
end;

select *, dbo.fnFormatString(string, [format])
from t

See working Fiddle