Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Convert data to decimal without using CAST and CONVERT

In this puzzle you need to convert the data to decimal without using CAST and CONVERT.For more details please check the sample input and expected output.

Sample Input

Id Vals
1 15%
2 15.9%
3 2%
4 0.2%
5 9%

Expected Output

Id Vals
1 0.1500000000000
2 0.1590000000000
3 0.0200000000000
4 0.0020000000000
5 0.0900000000000

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE Percents
(
	 Id Int
	,Vals Varchar(10)
)
GO

INSERT INTO Percents VALUES
(1,'15%'),
(2,'15.9%'),
(3,'2%'),
(4,'0.2%'),
(5,'9%')
GO

SELECT * 
FROM Percents

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1

--

DECLARE @ AS DECIMAL(10,2) = 100.
SELECT Id, REPLACE(Vals, '%', '') * (@/100.) / @  Vals
FROM Percents
 
--

Output-1

--

/*------------------------
DECLARE @ AS DECIMAL(10,2) = 100.
SELECT Id, REPLACE(Vals, '%', '') * (@/100.) / @  Vals
FROM Percents
------------------------*/
Id          Vals
----------- ---------------------------------------
1           0.1500000000000000000
2           0.1590000000000000000
3           0.0200000000000000000
4           0.0020000000000000000
5           0.0900000000000000000

(5 row(s) affected)

--

If you directly use the value then you will get the conversion error | Arithmetic overflow error converting varchar to data type numeric.

--
/*------------------------
SELECT Id, REPLACE(Vals, '%', '') * (1.) / (100.)  Vals
FROM Percents
------------------------*/
Id          Vals
----------- ---------------------------------------
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting varchar to data type numeric.
--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com