October 15, 2012 at 2:18 pm
I have 2 columns in the database. The value in column 1 is 10000000. The value in column 2 is 987. How would I update column 1 with the value of column 2 like this: 10009870. Thanks for all the help.
October 15, 2012 at 2:22 pm
Update table
set column1 = column1 + column2
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 15, 2012 at 2:38 pm
Thanks. I forgot to mention that these columns are string not integer.
October 15, 2012 at 2:50 pm
phamm (10/15/2012)
Thanks. I forgot to mention that these columns are string not integer.
Is there a reason they are not integer? You could try something like this...
DECLARE @col1 VARCHAR(20), @col2 VARCHAR(20)
SELECT @col1 = '10000000', @col2 = '9870'
SELECT CAST(CAST(@col1 AS INT) + CAST(@col2 AS INT) AS VARCHAR(20))
A couple of caveats though. Since they are strings (varchar?, char?) you may have some instances of leading zeros which will be lost, so you will have to deal with finding them and adding them back. Also, it will fail if there are any non-numeric data in your values.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 15, 2012 at 2:54 pm
You can try this :
update table
set column1 = convert(int,column1) + convert(int,column2)
Jeff.
October 15, 2012 at 3:01 pm
I don't have SQL in this computer right now, but something like this could solve the issue.
UPDATE table SET
column1 = STUFF( column1, LEN(column1) - LEN(column2) - 1,LEN(column2), column2)
October 15, 2012 at 3:03 pm
Thank you very much. what if my column1 have value 'abcdef0000' and column 2 value is 123. Then I cannot convert them to integer to add them together. In this case, how would I handle it, can I use replace, right, etc. which I tried to play around with but did not get the correct result which is 'abcdef01230'.
October 15, 2012 at 3:13 pm
phamm (10/15/2012)
Thank you very much. what if my column1 have value 'abcdef0000' and column 2 value is 123. Then I cannot convert them to integer to add them together. In this case, how would I handle it, can I use replace, right, etc. which I tried to play around with but did not get the correct result which is 'abcdef01230'.
Did you try STUFF as I suggested?
October 15, 2012 at 3:14 pm
hmmm, it's dirty but it's works fine.
;with CTE as (select * from table
where c1 like '%[0-9]%'
and c1 not like '%[a-z]%')
update cte
set c1 = convert(int,c1)+convert(int,c2)
where c2 like '%[0-9]%'
and c2 not like '%[a-z]%'
Jeff.
October 15, 2012 at 4:27 pm
You can try this too :
update table
set c1 = convert(int,c1)+convert(int,c2)
where isnumeric(c1) = 1
and isnumeric(c2)= 1
Jeff.
October 15, 2012 at 4:33 pm
jfdelette (10/15/2012)
You can try this too :
update tableset c1 = convert(int,c1)+convert(int,c2)
where isnumeric(c1) = 1
and isnumeric(c2)= 1
Jeff.
Why do you insist on converting strings to int when the OP has said that non numeric values should be combined as well?
Are you aware on the problems that might come up when using ISNUMERIC? If not, take a look at this http://www.sqlservercentral.com/articles/IsNumeric/71512/
October 16, 2012 at 1:12 am
phamm (10/15/2012)
Thank you very much. what if my column1 have value 'abcdef0000' and column 2 value is 123. Then I cannot convert them to integer to add them together. In this case, how would I handle it, can I use replace, right, etc. which I tried to play around with but did not get the correct result which is 'abcdef01230'.
As Luis has said, STUFF seems to be the best option for you
You should use REPLACE with extreme care as it will replace all the occurrences in the string at one go
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 16, 2012 at 1:45 am
Hi,
i had gone through your example its look wrong.
please find the detail.
declare @int1 varchar(100) = '100000000',
@int2 varchar(100) = '984541',
@int3 varchar(100)
your suggestion was mentioned below:
set @int3 = STUFF( @int1, LEN(@int1) - LEN(@int2) - 1,LEN(@int2), @int2)
print @int3
output is "198454100"
my suggestion mentioned below:
set @int3 = CAST(@int1 as INT) + CAST(@int2 as int)
print @int3
output is "100984541"
I am not sure what set of result this guy is looking for but please correct me if i am wrong here.
Thanks.
October 16, 2012 at 4:56 am
vivekkumar341 (10/16/2012)
Hi,i had gone through your example its look wrong.
please find the detail.
declare @int1 varchar(100) = '100000000',
@int2 varchar(100) = '984541',
@int3 varchar(100)
your suggestion was mentioned below:
set @int3 = STUFF( @int1, LEN(@int1) - LEN(@int2) - 1,LEN(@int2), @int2)
print @int3
output is "198454100"
my suggestion mentioned below:
set @int3 = CAST(@int1 as INT) + CAST(@int2 as int)
print @int3
output is "100984541"
I am not sure what set of result this guy is looking for but please correct me if i am wrong here.
Thanks.
Luis said that he has no SQL to test, so he made small miscalculation. His way is correct but should be done like that:
set @int3 = STUFF(@int1, LEN(@int1) - LEN(@int2) + 1, LEN(@int2), @int2)
+1 instead -1 for determining start position for STUFF.
Your suggestion is not appropriate as OP has stated that some values he has are not convertable to INT but he still want to do the same operation for them eg: 'abcd00000' and '123' should end up as 'abcd00123'. STUFF will do it, yours one will fail.
October 16, 2012 at 8:08 pm
Eugene Elutin (10/16/2012)
vivekkumar341 (10/16/2012)
Hi,i had gone through your example its look wrong.
please find the detail.
declare @int1 varchar(100) = '100000000',
@int2 varchar(100) = '984541',
@int3 varchar(100)
your suggestion was mentioned below:
set @int3 = STUFF( @int1, LEN(@int1) - LEN(@int2) - 1,LEN(@int2), @int2)
print @int3
output is "198454100"
my suggestion mentioned below:
set @int3 = CAST(@int1 as INT) + CAST(@int2 as int)
print @int3
output is "100984541"
I am not sure what set of result this guy is looking for but please correct me if i am wrong here.
Thanks.
Luis said that he has no SQL to test, so he made small miscalculation. His way is correct but should be done like that:
set @int3 = STUFF(@int1, LEN(@int1) - LEN(@int2) + 1, LEN(@int2), @int2)
+1 instead -1 for determining start position for STUFF.
Your suggestion is not appropriate as OP has stated that some values he has are not convertable to INT but he still want to do the same operation for them eg: 'abcd00000' and '123' should end up as 'abcd00123'. STUFF will do it, yours one will fail.
No, he didn't say that. He said 'abcd00000' and '123' should end up as 'abcd01230'. He also said that 'abcdef0000' and '123' should end up as 'abcdef01230'. So in one case stuff will work (with +0, not +1 or -1) and in the other case it needs a -1 on the third argument of stuff (still +0 on the second).
I suspect that one of these two is mis-stated by the OP, but I haven't a clue which.
Tom
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply