Solved

Ms SQL server 2005 - capture a decimal in variable

Posted on 2011-09-28
7
265 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
  • 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Review MS SQL cluster diagram 9 79
Truncate vs Delete 63 101
Analysis of table use 7 41
How to place a condition in a filter criteria in t-sql? 12 57
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

919 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now