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
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
First you know what the first column is going to be. In which case it's a simple
SET Column2 = Column1 - 1000000
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.
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!