Ms SQL server 2005 - capture a decimal in variable

I need to check my variables to see if they contain a decimal, '.'

Would that be a substring?

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

LVL 10
GlobaLevelProgrammerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Bhavesh ShahConnect With a Mentor Lead AnalysistCommented:

if @var like '%.%'
begin

end
0
 
Bhavesh ShahLead AnalysistCommented:
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
 
GlobaLevelProgrammerAuthor Commented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
GlobaLevelProgrammerAuthor Commented:
For the numerical(30,2)

What is the 30 and 2?

'2' being 2 decimal places?
0
 
Bhavesh ShahLead AnalysistCommented:
hi,

Int will remove your decimal value.

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

- Bhavesh
0
 
Bhavesh ShahLead AnalysistCommented:
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
 
GlobaLevelProgrammerAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.