How we can dynamically change this @ClientIDCode with @CodeMask string in SQL Server-CodePudding

How we can dynamically change this @ClientIDCode with @CodeMask string in SQL Server?

DECLARE @ClientIDCode varchar(20) = '1000003351', @CodeMask VARCHAR(50) = 'XXX-XXX'
DECLARE @ClientIDCode varchar(20) = '1000003351', @CodeMask VARCHAR(50) = 'XX-XXXX'
DECLARE @ClientIDCode varchar(20) = '1000003351', @CodeMask VARCHAR(50) = 'XX&XXXX'

I want an output like this:

1000003-351
100000-3351
100000&3351

CodePudding user response：

I'm not sure if this'll be more performant that John's using FORMAT (which is a known poor performer) but it doesn't involve changing the data types. You could split the strings into it's individual characters and then string aggregate, replacing non-X characters with the mask:

SELECT STRING_AGG(CASE SS.M WHEN 'X' THEN SS.C ELSE SS.M END,'') WITHIN GROUP (ORDER BY V.I DESC)
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20))V(I)
     CROSS APPLY (VALUES(SUBSTRING(REVERSE(@ClientIDCode),V.I,1),ISNULL(NULLIF(SUBSTRING(REVERSE(@CodeMask), V.I,1),''),'X')))SS(C,M)
WHERE SS.C != '';

This assumes @ClientIDCode can't be longer than 20 characters (as you define it as a varchar(20)). If it can be longer, then use an inline tally.

This also assumes you are on a supported version of SQL Server, as you haven't mentioned you aren't.

After testing, this is more performant on both my local host, work sandbox, and