Remove leading left zeros only

Transact-SQL
1

Experts, I have a field with mixtures of numbers such as

FIELD
000460
3688
245
00586
00590
10

I am trying to remove left zeros only to output

460
3688
245
586
590
10
I used the trim(replace) but this removed both beginning and end zeros.

2

convert() or cast() the value to integer

3

or TRY_CONVERT(int, FIELD) / TRY_CAST(FIELD AS int) which will not fail if a value cannot be converted to int, but will instead result in NULL.

1 Like
4

This should do it, I think:

SELECT SUBSTRING(FIELD, PATINDEX('%[^0]%', FIELD + ' '), LEN(FIELD) AS FIELD

1 Like
5

I am trying to merge this with other similar fields which are in strings. If I change to integer then it would mess up the joins. I'm trying to keep this as strong. Thanks

6

That's what I deliberately did in my code, sticking to string.

7

You're missing a trailing ), btw. Maybe he doesn't know how to correct it.

8

Oops, thanks:


SELECT SUBSTRING(FIELD, PATINDEX('%[^0]%', FIELD + ' '), LEN(FIELD)) AS FIELD
2 Likes
9

Change it to an integer to get rid of the zeroes and then change that integer back to a varchar.

1 Like
10

I don't think that's a good approach, since the value is not known to be a valid integer. I would definitely leave it as char.

11

If the values are known to contain non-numeric characters, then I absolutely agree. However, if they ARE supposed to contain only numerics, it's a great check using TRY_CONVERT()

1 Like
12

I'd say it the other way: unless the values are all known to be only valid numeric chars, then treat it as a string. And if non-numeric chars are allowed in a column, some will get in somehow.

Why try to force a conversion to int here? It's not needed. What if the column is twenty+ chars long? You might not be able to convert to a (big)int anyway.

1 Like
13

You missed what I was saying... if the column is SUPPOSED to be only numeric, then doing the convert to int will cause an error and you can take corrective action as a "bonus".

14

Actually I didn't miss that. I didn't state the explicitly, but that's just the wrong "solution".

If you need to check a column for numeric only, a trigger should automatically do that, you shouldn't have to coincidentally check it with every process that uses the column expecting an int value. That's not only wasteful, it risks wrong results, if someone later doesn't check it correctly.

15

Just use a CHECK constraint then. No need for a trigger.

If it is known that the column could have alpha or other non-numberics, you're code will also work fine. Consider replacing the LEN() function with an 8000... SQL knows when it has run out of characters.

16

Hmm, a CHECK would completely prevent the row from going in. Apparently such non-numeric values ARE allowed in the column, just not expected/preferred. Just as important, a CHECK could not remove the leading zeros if there were any, whereas a trigger could. And/or set another column to indicate whether the original value was a valid int or not.

17 
Hi 

Hope this helps 

SELECT 
    CAST(FIELD AS INT) AS CleanedField
FROM 
    YourTableName;