Vinay Vinay - 3 months ago 16
SQL Question

Pad Zero before first hypen and remove spaces and add BA and IN

I have data as below

98-45.3A-22
104-44.0A-23
00983-29.1-22
01757-42.5A-22
04968-37.3A2-23


Output Looking for output as below in SQL Server

00098-BA45.3A-IN-22
00104-BA44.0A-IN-23
00983-BA29.1-IN-22
01757-BA42.5A-IN-22
04968-BA37.3A2-IN-23

Answer

I splitted parts to cope with tricky data templates. This should work even with non-dash-2-digit tail:

WITH Src AS
(
    SELECT * FROM (VALUES
    ('98-45.3A-22'),
    ('104-44.0A-23'),
    ('00983-29.1-22'),
    ('01757-42.5A-22'),
    ('04968-37.3A2-23')
    ) T(X)
), Parts AS
(
    SELECT *,
    RIGHT('00000'+SUBSTRING(X, 1, CHARINDEX('-',X, 1)-1),5) Front,
    'BA'+SUBSTRING(X, CHARINDEX('-',X, 1)+1, 2) BA,
    SUBSTRING(X, PATINDEX('%.%',X), LEN(X)-CHARINDEX('-', REVERSE(X), 1)-PATINDEX('%.%',X)+1)  P,
    SUBSTRING(X, LEN(X)-CHARINDEX('-', REVERSE(X), 1)+1, LEN(X)) En
    FROM Src
)
SELECT Front+'-'+BA+P+'-IN'+En
FROM Parts

It returns:

00098-BA45.3A-IN-22
00104-BA44.0A-IN-23
00983-BA29.1-IN-22
01757-BA42.5A-IN-22
04968-BA37.3A2-IN-23