如何在SQL中快速重新索引列中的值

Question

我是SQL服务器的新手。现在我在一列中有一个值，它需要子串，然后重新定位这些值

Ex1: "000000000000000000000000000000" 
  => "000000000000000000000000000000"
Ex2: "168000000000000000000000000000"
  => "168000000000000000000000000000"
Ex3: "192000164000000000000000000000"
  => "192164000000000000000000000000"

示例#1和#2是可以的，因为我们什么都不做，但是示例#3非常困难

请帮助编写代码到子字符串并重新定位值作为输出示例#3

谢谢

Answer 1

由于我不知道您的SQL Server版本，而且您还没有说希望使用哪种语言，下面是SQL Server 2017的答案：

 Your Sample Data
WITH VTE AS(
    SELECT V.SomeString
    FROM (VALUES('000000000000000000000000000000'),
                ('168000000000000000000000000000'),
                ('192000164000000000000000000000')) V(SomeString)),
 The Solution
N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP 30 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) I
    FROM N N1, N N2),
NonZero AS(
    SELECT V.SomeString,
           T.I,
           SS.C
    FROM VTE V
        CROSS JOIN Tally T
        CROSS APPLY (VALUES(SUBSTRING(V.SomeString,T.I,1))) SS(C)
    WHERE SS.C != '0')
SELECT V.SomeString,
       LEFT(CONCAT(STRING_AGG(NZ.C,'') WITHIN GROUP (ORDER BY NZ.I),REPLICATE('0',30)),30) AS NewString
FROM VTE V
     LEFT JOIN NonZero NZ ON V.SomeString = NZ.SomeString
GROUP BY V.SomeString;

db<>fiddle