Remove all the leading zeros in a table using MS sql server-CodePudding

I have a table (t1) as shown below t1:

id       runs    wickets
001      200     0
024      60      5
276      180     1
022      369     2
000      120     2

From the above table I would like to remove all the leading zeros in the id column.

Note: if there is a row with all 0 as id value make it as single digit 0.

Expected Output:

id       runs    wickets
1        200     0
24       60      5
276      180     1
22       369     2
0        120     2

I tried below, but it did not work.

SELECT * 
FROM t1
SUBSTRING(id, patindex('%[^0]%',id), 10)

CodePudding user response：

I got the answer (shown below) with the help of @M.Ali comments and thank you so much @M.Ali and @Jeroen Mostert.

SELECT CAST(id AS INT) id, runs, wickets
FROM t1

CodePudding user response：

SELECT ABS(Id) FROM t1.
this code should work