Solved

SQL statement comparing float field to a string variable

Posted on 2008-10-07
10
546 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 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

685 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