Remove leading zeros with special character

Question

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.

Answer 1

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;

• Example db<>fiddle