November 4, 2010 at 1:41 pm
I'm migrating data from values in string format, but I need pass it to the new platform in numeric values.
ie: the original value it's lenght = '6*8' and I have a lot like that in my table.
I want to perform an operation that return de result in a easy way.
Something like this:
select '10.5*2' from my_table
result
---------------------------
21.00
select '10.5' from my_table --(sending just one value)
result
---------------------------
10.5
I thank you in advance.
November 4, 2010 at 1:46 pm
You will have to use dynamic SQL.
November 4, 2010 at 1:46 pm
Looks like it could be solved by explicitly selecting the string as part of a dynamic SQL statment.
Something like:
DECLARE @String VARCHAR(10), @Cmd VARCHAR(1000);
SELECT @String = '10*5';
SELECT @Cmd = 'select ' + @String;
EXEC(@Cmd);
SELECT @String = '10.5'
SELECT @Cmd = 'select ' + @String;
EXEC(@Cmd);
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 4, 2010 at 2:13 pm
Thanks,
I understand Dynamic SQL, but I need to get result to update another field in another table. The Dynamic SQL just execute it self on the fly and that's all.
How can I do that with Dynamic SQL?
ie:
create table mytable (mystrValue varchar(100), myNumberValue decimal(14,2) )
insert into mytable (mystrValue, myNumberValue) values ('6.00*2.00', 12)
insert into mytable (mystrValue, myNumberValue) values ('6.00*2*3', 36)
insert into mytable (mystrValue, myNumberValue) values ('6.00', 6)
select * from mytable
mystrValuemyNumberValue
6.00*2.0012.00 ---the value I need to get
6.00*2*336.00---the value I need to get
6.00 6.00---the value I need to get
Regards
November 4, 2010 at 2:17 pm
Something like this:
CREATE TABLE #T (
ID INT IDENTITY PRIMARY KEY,
RawVal VARCHAR(10),
CalcVal FLOAT);
DECLARE @String VARCHAR(10), @Cmd VARCHAR(1000);
SELECT @String = '10*5';
SELECT @Cmd = 'insert into #T select ''' + @String + ''', ' + @String;
EXEC(@Cmd);
SELECT @String = '10.5'
SELECT @Cmd = 'insert into #T select ''' + @String + ''', ' + @String;
EXEC(@Cmd);
SELECT *
FROM #T;
Of course, you're most likely going to use a cursor to assign values and execute the dynamic SQL instead of manually assigning, but the concept is the same.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 4, 2010 at 7:57 pm
The easiest way, as for me, would be creating a scalar function which whoud accept a string as a parameter and return a numeric value.
Function should parse the string and calculate the valuse according to arithmetic rules.
Function will also bring an advantage of some validation to be performed prior to calculation to prevent run-time errors.
Then the final code would look like this:
INSERT INTO TableNum
(ID, NumColumn)
SELECT ID, dbo.CalcFunction (StrColumn)
FROM TableStr
_____________
Code for TallyGenerator
November 5, 2010 at 6:27 am
That function is going to get really complex really fast if the data in the column has a complexity much higher than 2 numbers and one mathematical operator.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 5, 2010 at 7:21 am
Here is a CLR function which could be used in Sergiy's code:
http://www.pluralsight-training.net/community/blogs/dan/archive/2006/07/27/32597.aspx
or if the operators are simple enough, just use the XML capability.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply