[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Ms SQL server 2005 - capture a decimal in variable

Posted on 2011-09-28
Medium Priority
299 Views
I need to check my variables to see if they contain a decimal, '.'

Would that be a substring?

If @var = '.'
Begin
---code
End

0
Question by:GlobaLevel
• 4
• 3

LVL 19

Accepted Solution

Bhavesh Shah earned 2000 total points
ID: 36717256

if @var like '%.%'
begin

end
0

LVL 19

Expert Comment

ID: 36717272
Hi,

proper way is
``````declare @num numeric(30,2)
set @num = 25.50

if @num - floor(@num) > 0
select 'Decimal Value',@num - floor(@num)
``````
0

LVL 10

Author Comment

ID: 36717350
Wow that was easy, I didn't think tsql worked like that...can I ask one more question before I close.... I pull a value from a database via nvarchar '-3.5' but I need to recast so I can do math calculations, can I use INT?

Declare @Val nvarchar(max)
Set @Val = (select t from d where x='1')

If @Val like '%.%'
Begin
Declare @sum int
Set @sum = (cast(@Val as int) + 5.6))
end
0

LVL 10

Author Comment

ID: 36717399
For the numerical(30,2)

What is the 30 and 2?

'2' being 2 decimal places?
0

LVL 19

Expert Comment

ID: 36717404
hi,

Int will remove your decimal value.

Use decimal(20,2) or numeric(20,2)

- Bhavesh
0

LVL 19

Expert Comment

ID: 36717427
yes...

2 is precision value.

http://msdn.microsoft.com/en-us/library/ms187746.aspx

For all datatypes

http://msdn.microsoft.com/en-us/library/ms187752.aspx

0

LVL 10

Author Comment

ID: 36718698
okay some issue here...i am getting this error

Error converting data type varchar to numeric.

any ideas...

``````declare @home_spread nvarchar(max)

-- the value will look like '+33.5' or '-4.5 or +7'

-- ==================================================

-- =================================
-- IF NO DECIMAL EXISTS PROVIDE IT...
-- ======================================
begin
end
``````
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simpâ€¦
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Loops Section Overview
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calculâ€¦
###### Suggested Courses
Course of the Month18 days, 4 hours left to enroll