How Can I Split Decimal Value from Integer Value?

Assume i h开发者_Python百科ave 4.9 value.

I would like to split 4 and .9 ... How can i do it?

I could isolate 4 by using FLOOR(). What about the .9? How can i isolate it?

I'm using t-sql sql server 2005/2008

4.9%1

You can do modulus divide 1.

Declare @money money

Set @money = 418.75

Select  convert(int,@money - (@money % 1))  as 'LeftPortion'
       ,convert(int, (@money % 1) * 100)    as 'RightPortion'

The easiest i can think would be

SELECT IntegerPart = cast(4.9 AS int), DecimalPart = 4.9 - cast(4.9 AS int)

Many mainframe data importation processes (sadly common in the financial industry) require a fixed-width column input file which defines floating point numbers with a few columns for the integer value and a few columns for the decimal value. I.e. they want the number 4.019 represented as 000401900, where the first 4 chars correspond to the integer and the last 5 correspond to the decimal component.

For this application, you have to do a fair amount of string manipulation, but a good start is to get a query that gives you two fields containing 4 and '01900'.

Parsename() coupled with str() does the trick:

select integer_part = ltrim(str(4.019,25,0)), decimal_part = parsename(str(4.019, 25, 5),1)

str() pads with spaces on the left, so you need to ltrim for the integer. You might just cast to int here instead. Parsename just parses out period-delimited string tokens.

Here's a gotcha with this method. Be careful not to muddle the data type of your decimal_part column:

select integer_part = 1, decimal_part = 4
union all
select integer_part = ltrim(str(4.019,25,0)), decimal_part = parsename(str(4.019, 25, 5),1)

results in:

integer_part    decimal_part
1   4
4   1900

Here the intended '01900' became 1900. Thus:

select integer_part = 1, decimal_part = cast(4 as varchar(32))
union all
select integer_part = ltrim(str(4.019,25,0)), decimal_part = parsename(str(4.019, 25, 5),1)

results in:

integer_part    decimal_part
1   4
4   01900

Declare @dec decimal(8,2)

Set @dec = 23.98

Select Left(@dec, CharIndex('.',@dec)-1) as 'LeftPortion',
        RIGHT(@Dec, len(@dec) - CharIndex('.',@dec)) as 'RightPortion'

Take a look at the parsename function. I think the code below will produce the desired results. select parsename(4.9,2) gives the 4 select parsename(4.9,1) gives the 9 The output of parsename can be directly used as a number such that select parsename(4.9,2)* 2 give the vaule 8

Another method is

select 4.9-CAST(4.9 as int)

A way of converting using PatIndex is as follows

declare @val nvarchar(10) = '4.9'
select 
    LEFT(@val,PATINDEX(N'%[.]%', @val)-1) Int_Val,
    RIGHT( @val, len(@val) - PATINDEX(N'%[.]%', @val) + 1) Dec_Val

I've tried different ways but this one looks like the best that worked out for me pretty well.

-- For digits

Select  
Left(COLUMN_NAME, Case When CharIndex('.',COLUMN_NAME) = 0 Then 0 Else 
CharIndex('.',COLUMN_NAME)-1 END) as 'LeftPortion',  
RIGHT(COLUMN_NAME, len(COLUMN_NAME) - CharIndex('.',COLUMN_NAME)) as 
'RightPortion'  
From TABLE_NAME  

--To find Max Lengths

Select  
COLUMN_NAME,
  MAX(LEN(Left(COLUMN_NAME, Case When Ind = 0 Then 0 Else Ind-1 END))) as 
LenBeforedecimal,  
MAX(LEN(Right(COLUMN_NAME, LEN(COLUMN_NAME) - Ind))) as afterdecimal  
From  
(Select  
COLUMN_NAME,  
CHARINDEX('.', Cast(COLUMN_NAME as varchar(30))) as Ind  
From TABLE_NAME) a  
Group BY COLUMN_NAME

use below query: Using the PARSENAME function to split the delimited data

SELECT value , PARSENAME(value,2) AS int_part,PARSENAME(value,1) AS DECIMAL_ONLY FROM table name

example:4.9