SLK581 for SQL Server

SLK581.sql

CREATE Function [dbo].[StripNonAlphaCharacters](@Temp VarChar(50))
Returns VarChar(50)
AS
Begin
    Declare @KeepValues as varchar(50)
    Set @nonAlphaRegex = '%[^a-zA-Z]%'
    While PatIndex(@nonAlphaRegex, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@nonAlphaRegex, @Temp), 1, '')
    Return @Temp
End
GO

WITH linkage_fields AS (
SELECT
     CASE WHEN SURNAME IS NULL OR SURNAME = ''
              THEN '99999'
              ELSE dbo.StripNonAlphaCharacters(UPPER(SURNAME)) + '2222'
       END as l,
       CASE WHEN FIRST_N IS NULL OR FIRST_N = ''
              THEN '999'
              ELSE dbo.StripNonAlphaCharacters(UPPER(FIRST_N)) + '22'
       END as f,
     DOB,
     GENDER
FROM dbo.[patient identified table]
)

select SUBSTRING(l, 2, 1) + SUBSTRING(l, 3, 1) + SUBSTRING(l, 5, 1) + SUBSTRING(f, 2, 1) + SUBSTRING(f, 3, 1) + FORMAT(DOB, 'ddMMyyyy') + gender slk581
from linkage_fields