sql - 在第一个连字符之前填充零并删除空格并添加 BA 和 IN

Question

我有如下数据

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

输出在 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 1

我拆分了各个部分来应对棘手的数据模板。即使对于非破折号的 2 位尾部，这也应该有效:

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

它返回:

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