Better techniques for trimming leading zeros in SQL Server?

Cade_Roux_18255 0
Viewed 363891

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.

sql-server-2005 tsql sql-server sql string
18 Answers
SUBSTRING(str_col, PATINDEX('%[^0]%', str_col+'.'), LEN(str_col))
Arvo_357770

Why don't you just cast the value to INTEGER and then back to VARCHAR?

SELECT  CAST(CAST('000000000' AS INTEGER) AS VARCHAR)

--------
       0
user_551590

Instead of a space replace the 0's with a 'rare' whitespace character that shouldn't normally be in the column's text. A line feed is probably good enough for a column like this. Then you can LTrim normally and replace the special character with 0's again.

Joel_Coehoorn_30430 
SELECT CAST(CAST('000000000' AS INTEGER) AS VARCHAR)

This has a limit on the length of the string that can be converted to an INT

user_85771790

If you are using Snowflake SQL, might use this:

ltrim(str_col,'0')

The ltrim function removes all instances of the designated set of characters from the left side.

So ltrim(str_col,'0') on '00000008A' would return '8A'

And rtrim(str_col,'0.') on '$125.00' would return '$125'

user_40750850

This might help

SELECT ABS(column_name) FROM [db].[schema].[table]
user_15408640

For converting number as varchar to int, you could also use simple

(column + 0)
user_64981620

Very easy way, when you just work with numeric values:

SELECT
    TRY_CONVERT(INT, '000053830')
user_55287280

If you do not want to convert into int, I prefer this below logic because it can handle nulls IFNULL(field,LTRIM(field,'0'))

user_14825660 
  SUBSTRING(str_col, IIF(LEN(str_col) > 0, PATINDEX('%[^0]%', LEFT(str_col, LEN(str_col) - 1) + '.'), 0), LEN(str_col))

Works fine even with '0', '00' and so on.

user_86288240

In MySQL you can do this... 

Trim(Leading '0' from your_column)
user_48780880
Related