CODEWITHSUNDEEP
sql
user42348
user42348

Reputation: 4319

Splitting decimal in sql

I am getting result as decimal in stored procedure. If I am getting result as 123.45, I want to split it into 123 and 45. Can anybody help?

Upvotes: 0

Views: 3238

Answers (3)

Rapunzo
Rapunzo

Reputation: 986

try this;

DECLARE @result DECIMAL(8,2) = 123.45

SELECT CAST(round(@result,0) AS FLOAT)
SELECT REPLACE(@result % 1 ,'0.','')

OR

DECLARE @result decimal(8,2) = 123.45
select PARSENAME(@result, 2) AS LeftSideValue, PARSENAME(@result, 1) AS RightSideValue

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166446

You can also make use of ROUND instead of FLOOR.

See section C. Using ROUND to truncate for trucate, and then subtract that from the original.

Be aware that using FLOOR on negative numbers might not give you the required result.

Have a look at this example

DECLARE @Dec DECIMAL(12,8)

SET @Dec = -123.45

SELECT FLOOR(@DEc)

select round(@Dec, 0, 1)

Upvotes: 3

Axarydax
Axarydax

Reputation: 16603

use SQL function FLOOR() for getting integer part and subtract that from the original for the decimal part

Upvotes: 3

Related Questions