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.


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;


|       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.

| improve this answer | |

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
    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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.