SQL Server remove leading 0's and all characters after digits

Question

Hey I'm a java dev guys so I normally don't do this so bear with me.

I have a column in a table that looks like

Column
001 - test
012 - test 2

I need those values to look like like below when I do a select:

1
12
...
...

Thanks in advance

Answer 1

You want to convert the leading digits to a number. I would recommend:

select cast(left(column, patindex('%[^0-9]%', column) as decimal(38))

I chose decimal 38 because it is the largest exact numeric value (although int seems sufficient for your purposes).

You can also do this by removing initial zeros and leaving the result as a string:

select stuff(left(column, patindex('%[^0-9]%', column),
             1, patindex('%[^0]%', column) - 1, '')

And, if you know it is always three digits:

select cast(left(column, 3) as int)

and for a string:

select cast(cast(left(column, 3) as int) as varchar(3))