davelane
asked on
Searching Float columns with LIKE - Float to string cast
Hi there,
I have a float column that I need to search with LIKE /using of course some kind of string cast). The problem is that DB2 float to string cast, casts to scientific notation. I have tried casting to a decimal first, this helps, but pads the LHS with 0s. This causes problem when the numbers are negative. I have a column with the following value '-129.543'. I would like to be able to search with the following examples:
-129.5
9.54
9.543
-1
etc.
Something like
select char(decimal(wkz, 12, 4)) from lst.klk where char(decimal(wkz, 12, 4)) like '%129.5%'
works for all examples except those with a minus sign.
I have a float column that I need to search with LIKE /using of course some kind of string cast). The problem is that DB2 float to string cast, casts to scientific notation. I have tried casting to a decimal first, this helps, but pads the LHS with 0s. This causes problem when the numbers are negative. I have a column with the following value '-129.543'. I would like to be able to search with the following examples:
-129.5
9.54
9.543
-1
etc.
Something like
select char(decimal(wkz, 12, 4)) from lst.klk where char(decimal(wkz, 12, 4)) like '%129.5%'
works for all examples except those with a minus sign.
why cant you just do
where column between yourvalue - 0.000001 and yourvalue + 0.000001
preferably geting the range sorted out prior to the statement
so that the select can just do a between ...
e.g.
select yourvalue - 0.000001 , yourvalue + 0.000001
into fromvalue,tovalue
from sysibm.sysdummy1
select ...
from yourtable
where yourcolumn between fromvalue and tovalue
?
where column between yourvalue - 0.000001 and yourvalue + 0.000001
preferably geting the range sorted out prior to the statement
so that the select can just do a between ...
e.g.
select yourvalue - 0.000001 , yourvalue + 0.000001
into fromvalue,tovalue
from sysibm.sysdummy1
select ...
from yourtable
where yourcolumn between fromvalue and tovalue
?
ASKER
Hi,
I'm looking into the first suggestion. Thanks for the replies. It's a long story about why we need to be able to do this, the main point being we offer a search capability over a variety of columns of data in a table and we want to offer a consistent approach in every column.The users have gotten used to remembering only parts of the values in order to find stuff.
The search entry 456 should find both of the following:
12345.23
45678.28
Therefore a range is pretty much out of the question.
There must be some way of casting a float to a string and get the result in non-scientific notation?
I'm looking into the first suggestion. Thanks for the replies. It's a long story about why we need to be able to do this, the main point being we offer a search capability over a variety of columns of data in a table and we want to offer a consistent approach in every column.The users have gotten used to remembering only parts of the values in order to find stuff.
The search entry 456 should find both of the following:
12345.23
45678.28
Therefore a range is pretty much out of the question.
There must be some way of casting a float to a string and get the result in non-scientific notation?
ASKER
Hi there momi_saba,
I don't quite follow your suggestion. For the value -1.23 the SQL Statement
select decimal(wkz, 12, 4) from klk return the following:
-000000000001.2300
So doing the following '-' || char(decimal(abs(wkz), 12, 4)) gives out the exact same value. Thus the statement:
select
case
when
wkz >= 0
then
char(decimal(wkz, 12, 4))
else
'-' || char(decimal(abs(wkz), 12, 4))
end,
char(decimal(wkz, 12, 4))
from lst.klk
where
case
when
wkz >= 0
then
char(decimal(wkz, 12, 4))
else
'-' || char(decimal(abs(wkz), 12, 4))
end <>
char(decimal(wkz, 12, 4))
returns no values
I don't quite follow your suggestion. For the value -1.23 the SQL Statement
select decimal(wkz, 12, 4) from klk return the following:
-000000000001.2300
So doing the following '-' || char(decimal(abs(wkz), 12, 4)) gives out the exact same value. Thus the statement:
select
case
when
wkz >= 0
then
char(decimal(wkz, 12, 4))
else
'-' || char(decimal(abs(wkz), 12, 4))
end,
char(decimal(wkz, 12, 4))
from lst.klk
where
case
when
wkz >= 0
then
char(decimal(wkz, 12, 4))
else
'-' || char(decimal(abs(wkz), 12, 4))
end <>
char(decimal(wkz, 12, 4))
returns no values
ASKER
Upping points
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It's dirty, but I like it :)
select case
when wkz > 0 then char(decimal(wkz, 12, 4))
else "-"||char(decimal(abs(wkz,
end
from lst.klk
where case
when wkz > 0 then char(decimal(wkz, 12, 4))
else "-"||char(decimal(abs(wkz,
end like '%129.5%'
this way, if the number is positive you use your solution, if the number is negative you just convert it to positive and add the minus sign before the string
momi