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
1 Like