Code Answer: Better techniques for trimming leading zeros in SQL Server?

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.

From stackoverflow

Why don't you just cast the value to INTEGER and then back to VARCHAR?
```
SELECT  CAST(CAST('000000000' AS INTEGER) AS VARCHAR)

--------
       0
```
Joel Coehoorn : It's a string column, so I'd guess they're expecting non-numeric data from time to time. Something like an MRN number where the data is only _mostly_ numeric.

Cade Roux : Unfortunately, only works for numeric data, and sometimes the strings exceed the range for integer as well, so you'd have to use bigint.
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.
SUBSTRING(str_col, PATINDEX('%[^0]%', str_col+'.'), LEN(str_col))

Cade Roux : Clever, wish I'd thought of that.

Cade Roux : I'll still have to figure out how to fix up the length properly.

Arvo : What problem with length?

Cade Roux : Never mind, I realized that the '.' isn't in the substring because it's only use to find the pattern - it's even more clever than I thought.

Sung Meister : I can't think of a better way than Arvo's answer... I hate clever code usually but this seems like it is as good as it gets.

Code Answer