TSQL Format SSN/TIN (SQL Server 2016+)

Question

I need to format US SSN/TIN strings with dashes when they appear to be valid
(where valid = 9 digits) and otherwise return what is in the field (with leading 0s).

123456789 should format to 123-45-6789
and 3456789 formats to 003-45-6789

Can someone tell me why this code doesn't work?

Declare @TaxIDNum VarChar(11)
Set @TaxIDNum = '3456789'
Set @TaxIDNum = Right('0000'+@TaxIDNum,9)
Set @TaxIDNum = 
  CASE @TaxIDNum WHEN Len(@TaxIDNum)=9 THEN 
    CASE @TaxIDNum 
    WHEN IsNumeric(@TaxIDNum) 
    THEN Left(@TaxIDNum,3)+'-'+Right(Left(@TaxIDNum,5),2)+'-'+Right(@TaxIDNum,4) 
    ELSE @TaxIDNum END  -- return existing value
  ELSE @TaxIDNum END    -- return existing value
select @TaxIDNum, len(@TaxIDNum) as Length

I get a red squiggly error on the equals in "=9":
(Error text is: "Incorrect syntax near '='.")

Any solution that both works with a select or set, and solves the problem is welcome.

---

Thanks to @Larnu for mentioning Set @TaxIDNum=FORMAT(CONVERT(int,@TaxIDNum),'000-00-0000')

If I had error handling (our SSvr install doesn't) I would use Format. However if there is bad data, it breaks my query in a way from which I cannot recover.

Set @TaxIDNum=Replace('123-45-6789','-','') does avoid errors if the only data problem is that some of the rows are already formatted.

Answer 1

Seems like you could use FORMAT:

SELECT FORMAT(CONVERT(int,@TaxIDNum),'000-00-0000');