Recursively call STUFF for results in a table valued function

Question

This is a static query that kind of shows where I am trying to get

declare @Airbill VARCHAR(50) = '12345678912'
select stuff(stuff(@Airbill, 4, 0, '-'), 9, 0, '-')

But I need to make it more variable. I have the following varible:

DECLARE @AirbillMask VARCHAR(50) = '999-9999-9999'

Which can (and does) change. It is always '9's and '-'s. But the placement of them changes.

• If @AirbillMask is 999-99-99-9999 my desired result would be 123-45-67-8912.
• If @AirbillMask is 999-9999-9999 my desired result would be 123-4567-8912.

I am running dbo.FindPatternLocation(@AirbillMask, '-') that returns a table of dash locations.

I was planning to find a way to STUFF the parts of the @Airbill into it corresponding locations to produce a calculated field of FormattedAirbill, but I got stuck trying to iterate down into the STUFF calls.

Is there a way to call STUFF inside another STUFF call, over and over till I am out of rows?

I feel like a recursive CTE or cross apply could accomplish this, but I can't seem to wrap my head around it.

NOTE: I would prefer to do this without looping, if possible.

Answer 1

Your example is a big integer so assuming this is typical you can use FORMAT for this

declare @Airbill DECIMAL(38) = 12345678912


select FORMAT(@Airbill,   '000-0000-0000') AS A,
       FORMAT(@Airbill,  '000-00-00-0000') AS B,
       FORMAT(@Airbill, '00000-0000-0000') AS C,
       FORMAT(@Airbill, '#####-####-####') AS D;

Returns

+---------------+----------------+-----------------+---------------+
|       A       |       B        |        C        |       D       |
+---------------+----------------+-----------------+---------------+
| 123-4567-8912 | 123-45-67-8912 | 00123-4567-8912 | 123-4567-8912 |
+---------------+----------------+-----------------+---------------+

If it isn't possible to change the mask formats to use 0 or # instead of 9 it can obviously easily be done at run time with a single REPLACE of 9 to the desired character.

Answer 2

I was able to modify this answer to get to what I was looking for:

DECLARE @Airbill VARCHAR(50) = '12345678912'
DECLARE @AirbillMask VARCHAR(50) = '999-99-99-9999'

;WITH Airbill(Mask, Airbill) AS 
( 
    SELECT   @AirbillMask AS Mask, @Airbill AS Airbill
),
Position(Pos, Mask, Stuffed) AS 
( 
    SELECT   PATINDEX('%-%', Mask) AS Pos, Airbill.Mask, STUFF(Airbill.Airbill, PATINDEX('%-%', Mask), 0, '-')
    FROM     Airbill
    UNION ALL
    SELECT   Pos + PATINDEX('%-%', SUBSTRING(Mask, Pos + 1, LEN(mask))) Pos, mask, STUFF(Stuffed, Pos + PATINDEX('%-%', SUBSTRING(Mask, Pos + 1, LEN(Mask))), 0, '-')
    FROM     Position
    WHERE    PATINDEX('%-%', SUBSTRING(Mask, Pos + 1, LEN(mask))) > 0
)
SELECT  TOP 1 Stuffed
FROM    Position
ORDER BY Pos desc

I haven't performance tested it yet, but I do get the correct results.