Solved

Ms SQL server 2005 - capture a decimal in variable

Posted on 2011-09-28
7
283 Views
Last Modified: 2012-05-12
I need to check my variables to see if they contain a decimal, '.'

Would that be a substring?

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

0
Comment
Question by:GlobaLevel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 500 total points
ID: 36717256

if @var like '%.%'
begin

end
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
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)

Open in new window

0
 
LVL 10

Author Comment

by:GlobaLevel
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 10

Author Comment

by:GlobaLevel
ID: 36717399
For the numerical(30,2)

What is the 30 and 2?

'2' being 2 decimal places?
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 36717404
hi,

Int will remove your decimal value.

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

- Bhavesh
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 36717427
yes...

2 is precision value.

for more info on numeric datatype

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

by:GlobaLevel
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)


declare @home_spread_numeric numeric(30,2)

-- the value will look like '+33.5' or '-4.5 or +7'
set @home_spread = (select home_team_spread from schedule_shoe where shoe_id = @shoe_id)


-- ==================================================
set @home_spread = (replace(@home_spread,'-',''))

set @home_spread = (replace(@home_spread,'+',''))



-- =================================
-- IF NO DECIMAL EXISTS PROVIDE IT...
-- ======================================
if @home_spread not like '%.%'
begin
     set @home_spread_numeric = cast(@away_spread as numeric(30,2)) + '.0'
end

Open in new window

0

Featured Post

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Suggested Solutions

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question