SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


arithmetic operations over strings


arithmetic operations over strings

Author
Message
Earl Downs
Earl Downs
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 83
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.
Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5810 Visits: 11771
You will have to use dynamic SQL.
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23743 Visits: 9730
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
Earl Downs
Earl Downs
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 83
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
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23743 Visits: 9730
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
Sergiy
Sergiy
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10494 Visits: 11963
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


GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23743 Visits: 9730
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
Ken McKelvey
Ken McKelvey
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1685 Visits: 7911
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search