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

Why it does not work

From comments by [HABO][1]

You have mixed simple and searched case syntaxes, hence the complaint.
You should use LIKE (learn.microsoft.com/en-us/sql/t-sql/language-elements/…) with a suitable pattern, e.g. '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]', to determine if the string contains only nine numeric characters.

So I tried this, which also didn't work:

SET @TaxIDNum =   
  CASE @TaxIDNum Like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'  
  WHEN true 
  THEN  …

HABO:

You've hit an fiendish edge case.
There is a boolean data type (with values TRUE, FALSE and UNKNOWN), but you cannot get a firm grip on one:
"Unlike other SQL Server data types, a Boolean data type cannot be specified as the data type of a table column or variable, and cannot be returned in a result set."

You can't have CASE «boolean-expression» WHEN true THEN «result» END

Solution:

Use LIKE leaving the CASE expression empty

declare @TaxIDNum VarChar(11)  
set @TaxIDNum='3456789'  
set @TaxIDNum=right('0000'+@TaxIDNum,9)  
select @TaxIDNum  
SET @TaxIDNum = CASE 
  WHEN @TaxIDNum Like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'  
  THEN Left(@TaxIDNum,3)+'-'+SubString(@TaxIDNum,4,2)+'-'+Right(@TaxIDNum,4)  
  ELSE @TaxIDNum END  
select @TaxIDNum