Better techniques for trimming leading zeros in SQL Server?

Question

I've been using this for some time:

SUBSTRING(str_col, PATINDEX('%[^0]%', str_col), LEN(str_col))

However recently, I've found a problem with columns with all "0" characters like '00000000' because it never finds a non-"0" character to match.

An alternative technique I've seen is to use TRIM:

REPLACE(LTRIM(REPLACE(str_col, '0', ' ')), ' ', '0')

This has a problem if there are embedded spaces because they will be turned into "0"s when the spaces are turned back into "0"s.

I'm trying to avoid a scalar UDF. I've found a lot of performance problems with UDFs in SQL Server 2005.

Answer 1

Try using the below query:

SUBSTRING(str_col, PATINDEX('%[^0]%', str_col+'.'), LEN(str_col))

Answer 2

Here is the technique for trimming leading zeros in SQL:

SUBSTRING(str_col, PATINDEX('%[^0]%', str_col+'.'), LEN(str_col))

This will find the first non-zero character in ‘str_col, Adding '.’ ` will ensure that it always finds a position, even if the string contains all zeros.                                               

The whole code extracts the part of the string starting from the non-zero character till the end.

This technique will remove zeros from numeric strings and make the result even cleaner and more readable

Example:

 USE tempdb

GO

-- Create sample table

CREATE TABLE Table1 (Col1 VARCHAR(100))

INSERT INTO Table1 (Col1)

SELECT '0001'

UNION ALL

SELECT '000100'

UNION ALL

SELECT '100100'

UNION ALL

SELECT '000 0001'

UNION ALL

SELECT '00.001'

UNION ALL

SELECT '01.001'

UNION ALL

SELECT '0000'

GO

SELECT

CASE PATINDEX('%[^0 ]%', Col1 + ' ')

WHEN 0 THEN ''

ELSE SUBSTRING(Col1, PATINDEX('%[^0 ]%', Col1 + ' '), LEN(Col1))

END

FROM Table1

Answer 3

Use regular expression to remove all leading zeros. Use the following query

Select substring(columnname, PATINDEX('%[^0]%', columnname + '1'), LEN(str_col)) AS trimmedcolumn from tablename