SNIPPET
2 Upvotes

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.

COMMENTS
RELATED SNIPPETS
Search Snippets by Tag: