Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL statement comparing float field to a string variable

Posted on 2008-10-07
10
Medium Priority
?
556 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 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

971 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