January 14, 2008 at 10:45 am
I am trying to do what I thought was a simple query against a table,
SELECT * from dbo.mytable
where salesItem LIKE '8810657712'
However, nothing is returned?
When I change the LIKE clause to a =,
where salesItem = '8810657712'
data is returned.
The datatype of salesItem is float; when I do a len(salesItem) where salesItem = '8810657712', it returns 12.
Unfortunately, the application that running this query needs to accept either a '%' wildcard OR a value (such as 8810657712). All of the above testing was done via T-SQL in SSMS, SQL Server 2005. There is nothing funky about the collation, SQL_Latin1_General_CP1_CI_AS.
I admit, I am stumped at the moment! Any suggestions?
Thanks!
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
January 14, 2008 at 11:21 am
Yes, you need to add wild cards. Instead of simply
LIKE '42'
make it instead
LIKE '42%'
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 14, 2008 at 11:45 am
Unfortunately, it is not that easy. 🙁
I tried to do LIKE '8810657712' and LIKE '8810657712%' and LIKE '%8810657712%', none of which worked.
I also did a different query on the same table,
SELECT * from dbo.mytable
where InvoiceNumber LIKE 'H154788'
and data is returned correctly (no wildcards used). The InvoiceNumber is datatype "varchar(10)", LEN(InvoiceNumber) returned 7.
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
January 14, 2008 at 11:52 am
The LIKE operator is meant for use with character data, not numbers. Also, float is a very poor choice to use as an identifier.
This would be the best way to do the lookup:
where salesItem = 8810657712
January 14, 2008 at 11:58 am
The problem with floats is that they are imprecise, by definition.
Sorry about the bit of misdirection. I used it against a decimal several times with no issue. Works well in fact.
While agreeing that float is a bad identifier, if you already don't mind a bit of imprecision, how about simply using '>' or '<'?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 14, 2008 at 12:10 pm
I tried couple of experiments. I tried to cast it as Char. You will get results if you do a Like operation on the first digit. Example Like '8%' will work but if you try Like '88%' it wont work.
declare @i float
set @i = 8810657712.0
Select cast (@i as char(20)) . This will give the result 8.81066e+009
That is why when comparing for first number works and not for second.
-Roy
January 14, 2008 at 12:19 pm
From BOL:
Avoid using float or real columns in WHERE clause search conditions, especially the = and <> operators. It is best to limit float and real columns to > or < comparisons.
You're just going to have a hard time with that data type performing these types of operations.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 14, 2008 at 12:44 pm
This looks like a perfect example of using the str function, which converts a number to a string value.
declare @i float
set @i = 8810657712.0
Select str(@i)
January 15, 2008 at 7:46 am
Thanks everyone for your inputs!
Grant, I am impressed you were able to find that BOL entry. Either I am still not familiar enough with how to query BOL for information, or you have WAY too much time on your hand to be able to read BOL - hahaha. 😉
Seriously, thanks again for the analysis! I have sent an inquiry back to the developers to see if anyone knows WHY the datatype was set as FLOAT and not varchar.
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
January 15, 2008 at 8:28 am
You have to understand, BOL is my best friend because I'm too darned stupid to remember stuff.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 15, 2008 at 3:19 pm
Ok, in developing the code to convert all of the database tables that contain the FLOAT datatype fields to VARCHAR datatype fields, I discovered a solution to the initial QUERY issue:
SELECT * from dbo.mytable
where CAST(CAST(salesItem AS BIGINT) AS VARCHAR) LIKE '8810657712'
I tested the code and it will also work when the LIKE statement contains just a wild card, i.e., LIKE '%'.
SQL Server seems to think the field value is really 8810657712.00000 when doing a conversion, thus the conversion error? Converting to BIGINT first will do the initial truncation of the fractional portion of the field value.
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
January 15, 2008 at 3:55 pm
Ok, in developing the code to convert all of the database tables that contain the FLOAT datatype fields to VARCHAR datatype fields, I discovered a solution to the initial QUERY issue:
SELECT * from dbo.mytable
where CAST(CAST(salesItem AS BIGINT) AS VARCHAR) LIKE '8810657712'
I tested the code and it will also work when the LIKE statement contains just a wild card, i.e., LIKE '%'.
SQL Server seems to think the field value is really 8810657712.00000 when doing a conversion, thus the conversion error? Converting to BIGINT first will do the initial truncation of the fractional portion of the field value.
Interesting find. I learned something new 😀
January 16, 2008 at 12:48 pm
I hope I misunderstood what you are trying to do.
converting all you floats to varchar....
Doing the cast to do the queries, or possibly even creating a computed field to allow for indexing and like, but ALL FIELDS!!!!!
There are so many reasons why this is a bad idea. I suggest one thing thought since you are a newbie.
Keep a link to this site so that in 6 months when you DB is complete toast, you can get help changing it back.
January 16, 2008 at 1:02 pm
I hope I misunderstood what you are trying to do.
converting all you floats to varchar....
Doing the cast to do the queries, or possibly even creating a computed field to allow for indexing and like, but ALL FIELDS!!!!!
There are so many reasons why this is a bad idea. I suggest one thing thought since you are a newbie.
Keep a link to this site so that in 6 months when you DB is complete toast, you can get help changing it back.
The OP is trying to query a table for a specific set of rows using the like operator. The problem lies with how the developers created the table. The developers chose a FLOAT datatype for a sales item number. The problem is trying to use the like operator with a number. He has submitted a request to his development team to change the column to varchar, but in the interim was looking for a solution to satisfy the query.
While his interim solution works, he SHOULD and hopefully will correct the data type, instead of using the work around.
-Adam
January 16, 2008 at 1:09 pm
Fabio,
Thanks for your input and your concern.... who knows, maybe later down the line there will be some problem I am not cognizant of now?
The problem was with the original table design in the database. The data coming into the table is text data, not float. Why the original designer (no one wants to own up to the design now) decided to make two of the fields FLOAT is unknown.
If the final decision is to correct the table(s) in the database (gets my vote), we will (1) create a new field, (2) copy the data to the new field using the same technique mentioned previously (double cast) (3) delete the original field (4) rename the new field to the field that was deleted.
BTW, this is NOT the index field, nor is it a unique identifier. 🙂
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply