Solved

SQL statement comparing float field to a string variable

Posted on 2008-10-07
10
543 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
10 Comments
 
LVL 142

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 92

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
 

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 142

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

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

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

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 142

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
A short film showing how OnPage and Connectwise integration works.

929 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