sqlsql-serversql-server-2019

Remove leading zeros with special character


I have a column Device in a SQL Server table:

Device
DC_003_08TX
DC_010_04TX
DC_100_10TX
DC_004_00TX
DC_120_119TX

I'm trying to write a query that removes the first 3 characters (DC_), removes the last non integer characters, and also replaces _ with -.

This query does just that:

SELECT 
    REPLACE (SUBSTRING(Device, 4, LEN(Device) - PATINDEX('%[0-9]%', REVERSE(Device)) - 3 + 1), '_', '-') AS Device

Now, I need to modify this query to remove the leading zeros from the columns and if there are double zeros behind the -, remove the double zeros and the -.

For example, the table above should now look like below:

DeviceNew
3-8
10-4
100-10
4
120-119

Thanks for the help.


Solution

  • One way on < 2022:

      WITH agg AS
      (
        SELECT Device, 
               a = NULLIF(MAX(a), 0), 
               b = NULLIF(MAX(b), 0)
        FROM
        (
          SELECT Device,
             a = CASE WHEN [key] = 1 THEN TRY_CONVERT(int, value) END,
             b = CASE WHEN [key] = 2 THEN TRY_CONVERT(int, 
                 LEFT(value, LEN(value) - PATINDEX('%[0-9]%', 
                 REVERSE(value)) + 1)) END
          FROM dbo.[a SQL Server table] 
          CROSS APPLY 
          OPENJSON(CONCAT('["', REPLACE(Device, '_', '","'), '"]"')) AS j
          WHERE j.[key] IN (1,2)
        ) AS src
        GROUP BY Device
      )
      SELECT Device, 
        CONCAT(a, CASE WHEN a > 0 AND b > 0 THEN '-' END, b)
      FROM agg;