• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

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

0
GlobaLevel
Asked:
GlobaLevel
  • 4
  • 3
1 Solution
 
Bhavesh ShahLead 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
 
GlobaLevelAuthor 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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
GlobaLevelAuthor 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
 
GlobaLevelAuthor 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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now