206

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.

Community
  • 1
  • 1
Cade Roux
  • 88,164
  • 40
  • 182
  • 265
  • Is the rest of the string always going to contain just 'numeric' chars, or might you have alphas too? If it's just numeric data, then Quassnoi's suggestion of casting to an integer and back seems like a good one. – robsoft Mar 19 '09 at 14:26
  • It's a general technique. These are typically account numbers which are coming in an unconformed field and I need to ensure they match the conformation rules the data warehouse uses in their ETL (which is, of course in the much more full-featured SSIS environment, I assume they use .TrimStart). – Cade Roux Mar 19 '09 at 14:36
  • I had an issue with alpahanumeric values, that where padded with zeros - used the cast(column as numeric), but it crashed when reaching a non-numeric value. Then I tried Trim('0' from column) - and it worked fine. The database was an azure SQL database. – theodor.johannesen Mar 14 '23 at 15:35

19 Answers19

375
SUBSTRING(str_col, PATINDEX('%[^0]%', str_col+'.'), LEN(str_col))
Justin
  • 9,634
  • 6
  • 35
  • 47
Arvo
  • 10,349
  • 1
  • 31
  • 34
  • I'll still have to figure out how to fix up the length properly. – Cade Roux Mar 19 '09 at 14:39
  • 4
    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 Mar 19 '09 at 14:50
  • 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. – dance2die Mar 20 '09 at 04:19
  • 2
    Encapsulating this in a function resulted in slowing down my queries. I'm not quite sure why but I think it has to do with type conversion. Using the SUBSTRING inline was much faster. – Ronnie Overby Jul 26 '13 at 16:22
  • 1
    The Question states the problem with this is when you parse a zero ('0'), you get a blank. You need to be able to tell the difference between a '0' value and a blank value. Please see my post for a full solution: http://stackoverflow.com/a/21805081/555798 – MikeTeeVee Feb 17 '14 at 18:15
  • 1
    Ronnie Overby: If you use it in function you need to return part of the query and not the result. I read good article about it but dont know url now. But you are right. – Muflix Feb 13 '15 at 20:41
  • @Arvo clever solution; however, I fail to understand why you have the `+'.'` in the `PATINDEX` function. I had the need to solve the same problem, and it works the same with or without that piece. – Eli Jan 03 '18 at 15:33
  • 1
    @Eli - Like stated in OP, without adding `'.'` (or any other symbol) original code fails to process strings, consisting only from zeroes. – Arvo Jan 04 '18 at 07:10
  • Hey @Arvo, that's what I figured would have been the cause, though I did test it on a string of just zeros and it worked fine. I'm using SQL Server 2016, and I'm curious to know what changed over the versions to make it not blow up. – Eli Jan 05 '18 at 14:30
  • @JotaPardo The code does *NOT* fail with `@str = '0034E - 00-0'`. It results `'34E - 00-0'` as expected. What do you expect it to result? – Diego Queiroz Jul 31 '18 at 00:36
59

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
  • 413,100
  • 91
  • 616
  • 614
  • 22
    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 Mar 19 '09 at 14:25
  • 1
    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 Mar 19 '09 at 14:40
  • 3
    `SELECT CASE ISNUMERIC(str_col) WHEN 1 THEN CAST(CAST(str_col AS BIGINT) AS VARCHAR(255)) ELSE str_col END` – Yuriy Rozhovetskiy Apr 10 '13 at 14:04
  • Even with `BIGINT`, some types of string will still fail this conversion. Consider `0001E123` for example. – roaima Sep 02 '15 at 12:14
  • @roaima: if we had `TRY_CONVERT` back in 2009 I would definitely suggest that. – Quassnoi Sep 02 '15 at 12:28
  • Sure, but even six years on the conversion attempt will (at best) still fail to remove the leading zeros. – roaima Sep 02 '15 at 13:04
  • SELECT ISNUMERIC(',') and SELECT ISNUMERIC('.') both return true. If either of these values (and there are a few others like $) are in your value, the code from Yuriy will fail to stop before casting and will error. The same goes for legitimate decimal/float values. – Chris Porter Oct 09 '15 at 15:41
  • 1
    From my testing (and experience) this is a relatively costly operation in comparison with the accepted answer. For performance reasons, it's best to avoid changing data types, or comparing data of different types, if it is within your power to do so. – reedstonefood Nov 03 '15 at 15:36
  • This worked a treat for my requirements :) – S8Tony Jul 07 '21 at 14:33
  • Can anybody help to understand purpose of that dot ? I tested with and without, no differences, can replace it with any other Char or remove it. `PATINDEX('%[^0]%', str_col+'.')` – Mich28 Nov 04 '22 at 19:49
  • @Mich28: compare `PATINDEX('%[^0]%', '0')` and `PATINDEX('%[^0]%', '0.')` – Quassnoi Nov 05 '22 at 02:54
18

Other answers here to not take into consideration if you have all-zero's (or even a single zero).
Some always default an empty string to zero, which is wrong when it is supposed to remain blank.
Re-read the original question. This answers what the Questioner wants.

Solution #1:

--This example uses both Leading and Trailing zero's.
--Avoid losing those Trailing zero's and converting embedded spaces into more zeros.
--I added a non-whitespace character ("_") to retain trailing zero's after calling Replace().
--Simply remove the RTrim() function call if you want to preserve trailing spaces.
--If you treat zero's and empty-strings as the same thing for your application,
--  then you may skip the Case-Statement entirely and just use CN.CleanNumber .
DECLARE @WackadooNumber VarChar(50) = ' 0 0123ABC D0 '--'000'--
SELECT WN.WackadooNumber, CN.CleanNumber,
       (CASE WHEN WN.WackadooNumber LIKE '%0%' AND CN.CleanNumber = '' THEN '0' ELSE CN.CleanNumber END)[AllowZero]
 FROM (SELECT @WackadooNumber[WackadooNumber]) AS WN
 OUTER APPLY (SELECT RTRIM(RIGHT(WN.WackadooNumber, LEN(LTRIM(REPLACE(WN.WackadooNumber + '_', '0', ' '))) - 1))[CleanNumber]) AS CN
--Result: "123ABC D0"

Solution #2 (with sample data):

SELECT O.Type, O.Value, Parsed.Value[WrongValue],
       (CASE WHEN CHARINDEX('0', T.Value)  > 0--If there's at least one zero.
              AND LEN(Parsed.Value) = 0--And the trimmed length is zero.
             THEN '0' ELSE Parsed.Value END)[FinalValue],
       (CASE WHEN CHARINDEX('0', T.Value)  > 0--If there's at least one zero.
              AND LEN(Parsed.TrimmedValue) = 0--And the trimmed length is zero.
             THEN '0' ELSE LTRIM(RTRIM(Parsed.TrimmedValue)) END)[FinalTrimmedValue]
  FROM 
  (
    VALUES ('Null', NULL), ('EmptyString', ''),
           ('Zero', '0'), ('Zero', '0000'), ('Zero', '000.000'),
           ('Spaces', '    0   A B C '), ('Number', '000123'),
           ('AlphaNum', '000ABC123'), ('NoZero', 'NoZerosHere')
  ) AS O(Type, Value)--O is for Original.
  CROSS APPLY
  ( --This Step is Optional.  Use if you also want to remove leading spaces.
    SELECT LTRIM(RTRIM(O.Value))[Value]
  ) AS T--T is for Trimmed.
  CROSS APPLY
  ( --From @CadeRoux's Post.
    SELECT SUBSTRING(O.Value, PATINDEX('%[^0]%', O.Value + '.'), LEN(O.Value))[Value],
           SUBSTRING(T.Value, PATINDEX('%[^0]%', T.Value + '.'), LEN(T.Value))[TrimmedValue]
  ) AS Parsed

Results:

MikeTeeVee_SQL_Server_Remove_Leading_Zeros

Summary:

You could use what I have above for a one-off removal of leading-zero's.
If you plan on reusing it a lot, then place it in an Inline-Table-Valued-Function (ITVF).
Your concerns about performance problems with UDF's is understandable.
However, this problem only applies to All-Scalar-Functions and Multi-Statement-Table-Functions.
Using ITVF's is perfectly fine.

I have the same problem with our 3rd-Party database.
With Alpha-Numeric fields many are entered in without the leading spaces, dang humans!
This makes joins impossible without cleaning up the missing leading-zeros.

Conclusion:

Instead of removing the leading-zeros, you may want to consider just padding your trimmed-values with leading-zeros when you do your joins.
Better yet, clean up your data in the table by adding leading zeros, then rebuilding your indexes.
I think this would be WAY faster and less complex.

SELECT RIGHT('0000000000' + LTRIM(RTRIM(NULLIF(' 0A10  ', ''))), 10)--0000000A10
SELECT RIGHT('0000000000' + LTRIM(RTRIM(NULLIF('', ''))), 10)--NULL --When Blank.
MikeTeeVee
  • 18,543
  • 7
  • 76
  • 70
  • 4
    @DiegoQueiroz If the answer is wrong, then please downrank and explain why it doesn't work. If the answer works, but is too comprehensive for you, then please do not downrank me or other members on this site. Thank you for the comment. It is good feedback to hear - I say this sincerely. – MikeTeeVee Jan 24 '17 at 21:39
6

My version of this is an adaptation of Arvo's work, with a little more added on to ensure two other cases.

1) If we have all 0s, we should return the digit 0.

2) If we have a blank, we should still return a blank character.

CASE 
    WHEN PATINDEX('%[^0]%', str_col + '.') > LEN(str_col) THEN RIGHT(str_col, 1) 
    ELSE SUBSTRING(str_col, PATINDEX('%[^0]%', str_col + '.'), LEN(str_col))
 END
Brisbe
  • 1,588
  • 2
  • 20
  • 42
6

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
  • 399,467
  • 113
  • 570
  • 794
  • This sounds like a proposal to do something like: `REPLACE(LTRIM(REPLACE(str_col, '0', '~')), '~', '0')` This will simply left trim spaces and will not remove any zeros. You would need additional logic to remove the special characters, but only if they precede a non-zero. – Larryjl Oct 16 '22 at 00:36
4

This makes a nice Function....

DROP FUNCTION [dbo].[FN_StripLeading]
GO
CREATE FUNCTION [dbo].[FN_StripLeading] (@string VarChar(128), @stripChar VarChar(1))
RETURNS VarChar(128)
AS
BEGIN
-- http://stackoverflow.com/questions/662383/better-techniques-for-trimming-leading-zeros-in-sql-server
    DECLARE @retVal VarChar(128),
            @pattern varChar(10)
    SELECT @pattern = '%[^'+@stripChar+']%'
    SELECT @retVal = CASE WHEN SUBSTRING(@string, PATINDEX(@pattern, @string+'.'), LEN(@string)) = '' THEN @stripChar ELSE SUBSTRING(@string, PATINDEX(@pattern, @string+'.'), LEN(@string)) END
    RETURN (@retVal)
END
GO
GRANT EXECUTE ON [dbo].[FN_StripLeading] TO PUBLIC
Fung
  • 3,508
  • 2
  • 26
  • 33
user2600313
  • 41
  • 1
  • 1
    This will also return zero when the value has no zeros (is blank). This Answer also uses a multi-statement-scalar-function, when the Question above specifically states to avoid using UDF's. – MikeTeeVee Feb 15 '14 at 23:48
4

cast(value as int) will always work if string is a number

tichra
  • 557
  • 5
  • 18
  • This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - [From Review](/review/low-quality-posts/11986609) – Josip Ivic Apr 12 '16 at 14:35
  • 1
    infact it is an answer because it does work? answers do not need to be lengthy – tichra Apr 13 '16 at 09:27
  • You are correct that answers don't need to be lengthy, however they should be complete if possible, and your answer isn't; it changes the data type of the result. I believe this would have been a better response: SELECT CAST(CAST(value AS Int) AS VARCHAR). You also should mention that you'll get an error with Int if the computed value exceeds 2.1x10^9 (eight digit limit). Using BigInt you get the error if the value exceeds about 19 digits (9.2x10^18). – J. Chris Compton Feb 13 '18 at 16:14
4
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

CDspace
  • 2,639
  • 18
  • 30
  • 36
Curt Ehrhart
  • 71
  • 2
  • Can you explain a bit more in your answer as to why you think this will work? What would happen if this was a non-zero number with a bunch of leading zeros? – Taegost Jan 29 '18 at 21:37
  • If your numbers are 18 digits or less (and most 19 digit numbers work because the limit is actually 9.2x10^18) you can use SELECT CAST(CAST(@Field_Name AS BigInt) AS VARCHAR) to get rid of leading zeros. NOTE: this will fail if you have non-numeric characters (dash, letter, period, etc.) with error msg 8114 "Error converting data type varchar to bigint." – J. Chris Compton Feb 13 '18 at 15:47
4

The following will return '0' if the string consists entirely of zeros:

CASE WHEN SUBSTRING(str_col, PATINDEX('%[^0]%', str_col+'.'), LEN(str_col)) = '' THEN '0' ELSE SUBSTRING(str_col, PATINDEX('%[^0]%', str_col+'.'), LEN(str_col)) END AS str_col
Scott
  • 6,411
  • 6
  • 39
  • 43
3

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'

JJFord3
  • 1,976
  • 1
  • 25
  • 40
3

This might help

SELECT ABS(column_name) FROM [db].[schema].[table]
Asingwire Cuthbert
  • 98
  • 6
2
replace(ltrim(replace(Fieldname.TableName, '0', '')), '', '0')

The suggestion from Thomas G worked for our needs.

The field in our case was already string and only the leading zeros needed to be trimmed. Mostly it's all numeric but sometimes there are letters so the previous INT conversion would crash.

Randy
  • 29
  • 1
1
  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.

Lisandro
  • 89
  • 1
  • 4
1

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

(column + 0)
Lukáš Klíma
  • 104
  • 5
1

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

SELECT
    TRY_CONVERT(INT, '000053830')
SaSH_17
  • 405
  • 5
  • 15
0

Try this:

replace(ltrim(replace(@str, '0', ' ')), ' ', '0')
Thomas G
  • 9,886
  • 7
  • 28
  • 41
Shetty
  • 19
0

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

shockwave
  • 3,074
  • 9
  • 35
  • 60
0

Starting with SQL Server 2022 (16.x) you can do this

TRIM ( [ LEADING | TRAILING | BOTH ] [characters FROM ] string )

Xu Shaoyang
  • 71
  • 5
-3

In MySQL you can do this... 

Trim(Leading '0' from your_column)
joe_evans
  • 120
  • 5