Remove Leading Zeros from a Field with T-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
By GregHe1979 - Last Updated Dec. 1, 2020, 3:13 p.m.