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
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14751 Visits: 11848
You will have to use dynamic SQL.
GSquared
GSquared
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58733 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
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 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 Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58733 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
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25943 Visits: 12476
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 Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58733 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
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4137 Visits: 8444
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