Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

arithmetic operations over strings Expand / Collapse
Author
Message
Posted Thursday, November 4, 2010 1:41 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 17, 2012 7:04 PM
Points: 11, Visits: 75
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.
Post #1016187
Posted Thursday, November 4, 2010 1:46 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, September 18, 2014 12:39 AM
Points: 3,105, Visits: 11,494
You will have to use dynamic SQL.


Post #1016196
Posted Thursday, November 4, 2010 1:46 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #1016197
Posted Thursday, November 4, 2010 2:13 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 17, 2012 7:04 PM
Points: 11, Visits: 75
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

mystrValue myNumberValue
6.00*2.00 12.00 ---the value I need to get
6.00*2*3 36.00---the value I need to get
6.00 6.00---the value I need to get

Regards
Post #1016222
Posted Thursday, November 4, 2010 2:17 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #1016225
Posted Thursday, November 4, 2010 7:57 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, September 18, 2014 11:10 PM
Points: 4,573, Visits: 8,351
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

Post #1016355
Posted Friday, November 5, 2010 6:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
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
Post #1016480
Posted Friday, November 5, 2010 7:21 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, September 18, 2014 4:53 AM
Points: 848, Visits: 5,507
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.
Post #1016523
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse