Remove Leading Zeros from a Field with T-SQL
SQL
In this example we use the code below to strip out all leading zeros from the PRODUCT_ID field. This is dynamic so will remove anything all zeros even if there is an inconsistent number of leading zeros in the field. Other leading characters can be removed by replacing the 0.
/*SYNTAX*/
SUBSTRING(FIELD, PATINDEX('%[^0]%',FIELD + '-'), LEN(FIELD))
/*EXAMPLE*/
SELECT
ORDER_NO,
SUBSTRING(PRODUCT_ID, PATINDEX('%[^0]%',PRODUCT_ID + '-'), LEN(PRODUCT_ID)) AS PRODUCT_ID
FROM
ORDERS
6
5
5
4
4
4