0

I have a column containing a combination of numeric and alphanumeric values. When the value is strictly numeric, the database stores it with leading zeroes (but not always), but not if not.

Here's some sample data:

enter image description here

I need to use these values as part of a string that I will use to join to another table. Unfortunately, the portion of the string that corresponds to this field in the other table snips off the leading zeroes of any of the numeric-only values. I'm stumped finding a method of snipping the leading zeroes ONLY in this case.

I found this solution, but it's not for SQL Server (2012). Trim leading zeroes if it is numeric and not trim zeroes if it is alphanumeric

I also saw this, but it also removes the leading zeroes from the hyphenated values shown in the example, which doesn't work. Better techniques for trimming leading zeros in SQL Server?

Help! Thanks!

ABrown78
  • 29
  • 6

2 Answers2

0

You could use:

select (case when col not like '%[^0-9]%'
             then convert(varchar(255), try_convert(numeric(38), col))
             else col
        end)

This works for up to 38 digits after the leading zeros

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

The database does not store anything in varchar (text) fields except what you give it. If you give it leading zeroes, it will save them, it has no reason not to as it's just a piece of text.

For your problem, you can do this:

ISNULL(CAST(TRY_CAST(field AS numeric(38)) AS varchar(insert_field_length))), field)

Charlieface
  • 52,284
  • 6
  • 19
  • 43