question

Maist2020 avatar image
Maist2020 asked

Select equation answer as number

I have a table that contains a column with values stored as varchars (text) as following

150/5

200/5

.....

I need to select the column to return

30

40

select
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Is this the only sort of calculation, or are you after something more comprehensive?

0 Likes 0 ·

Just one type of calculation

Just division

0 Likes 0 ·

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered

If you always have one nominator and one denominator, than something like this could work.

DECLARE @t TABLE(s VARCHAR(100));
INSERT @t (s)
VALUES 
('150/5'),
('200/5');
WITH CTE AS(
 SELECT 
 CAST(LEFT(s,CHARINDEX('/',s)-1) AS INT) AS first,
 CAST(SUBSTRING(s,CHARINDEX('/',s)+1,LEN(s)) AS INT) AS second 
 FROM @t
)SELECT first,second,first/second FROM cte;

Guess there are more elegant ways of doing it, and this would break if there's not exactly one '/' in the column.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.