Solved

SQL statement comparing float field to a string variable

Posted on 2008-10-07
10
549 Views
Last Modified: 2012-08-13
I'm trying to write a SQL select statement that will compare a field in my database (type float) to a parameter that I'm passing from my ASP page (type string or nvarchar).  What am I doing wrong?
CAST(table.field AS nvarchar) LIKE '%' + @SearchValue + '%')

Open in new window

0
Comment
Question by:ryanvb83
[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
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22659866
this should work better:
CAST(table.field AS nvarchar(1000)) LIKE '%' + @SearchValue + '%')

Open in new window

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22659870
or this...

CAST(table.field AS nvarchar(max)) LIKE '%' + @SearchValue + '%')
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22659871
Hello ryanvb83,

Are you getting an error, or just no matches?

BTW, you may be better off converting that float to a numeric(x, y) type first, to make sure it's not a problem
of matching up too many decimal places...

Regards,

Patrick
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.

 

Author Comment

by:ryanvb83
ID: 22660030
Hmm...I can't tell if either of those two solutions worked.  I'm not getting an error message.  I'm not receiving any matches.  The float field contains a 10-digit number and I'm trying to match it up to a parameter that is entered as a string.  The SQL statement checks the parameter against a string field (containing someone's name) and an account field (containg their account number).  I'm trying to just have one search field for the user that searches both the name and account number field.  Do you have any other ideas?  When I type a string into the parameter, it finds the customer's name.  But, I can't get any results when I search by account number.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22660052
can you show what this does return?

select CAST(table.field AS nvarchar(1000)) from table

for that float field, and what is the value of @SearchValue in case you want to match ...
0
 

Author Comment

by:ryanvb83
ID: 22660085
Ahha...OK, its returning 1.23456e+009

This number is actually 1234567890 (10 digits).
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22660152
ok:

select CAST(CAST(table.field AS DECIMAL(26,4)) AS nvarchar(1000)) from table

Open in new window

0
 

Author Comment

by:ryanvb83
ID: 22660215
Is there any significance to having 4 spaces after the decimal point?  I only need the first 10 digits.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 22660251
you could cast to bigint:
select CAST(CAST(table.field AS bigint) AS nvarchar(1000)) from table

Open in new window

0
 

Author Closing Comment

by:ryanvb83
ID: 31503861
Thanks!
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

635 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