views:

902

answers:

3

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.

+2  A: 

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

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

--------
       0
Quassnoi
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.
Joel Coehoorn
Unfortunately, only works for numeric data, and sometimes the strings exceed the range for integer as well, so you'd have to use bigint.
Cade Roux
+2  A: 

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
+4  A: 

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

Arvo
Clever, wish I'd thought of that.
Cade Roux
I'll still have to figure out how to fix up the length properly.
Cade Roux
What problem with length?
Arvo
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.
Cade Roux
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.
Sung Meister