How to trim first digit off an integer value?

Question

i have data like Column1, i want to convert the Column1 data as Column2. Can any one suggest to me how i can achieve this in SQL?

Column 1    Column2
1000001         1
1000002         2 
1000003         3
1000004         4
1000005         5

Answer 1

Two options.

First you know what the first column is going to be. In which case it's a simple

SET Column2 = Column1 - 1000000

or

SET Column2 = Column1 % 1000000

The second has the benefit that the digit could be 1-9 with no problems.

If you have no idea what that first place is going to be and you still only want to get rid of that one value you could do something like this.

declare @test int = 1005
select CAST(SUBSTRING(CAST(@test as varchar(50)),2,999) AS int)

If you have to allow for negative numbers it gets a bit more complicated.

declare @test int = -1005
select CASE WHEN @test < 0 THEN -1 ELSE 1 END *
        CAST(SUBSTRING(CAST(ABS(@test) as varchar(50)),2,999) AS int)

Obviously this could get rather slow for big sets of data.

Answer 2

assuming the pattern in your example Column 1 is constant (begins w/1 followed by some number of zeros) then it should be a simple matter of converting your int to text, removing the left-most character, and then converting back to int. The variable-length run of zeros should be disregarded in the process. Something like:

Cast(Ltrim(Cast(Column 1 As text)), '1') As int

might work but could fail with a value like 1001010, so maybe something more like:

Cast(Trim(Leading '1' From Cast(Column 1 As Text)))As int

Seems this should take a numeric value of 1001010 and convert it to a text value of '001010' which then becomes the numeric value of 1010 when it is re-cast.

Hope that helps!