Solved

SQL statement comparing float field to a string variable

Posted on 2008-10-07
10
544 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
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 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
 

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

770 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