Solved

# Searching Float columns with LIKE - Float to string cast

Posted on 2007-03-20
Medium Priority
865 Views
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.
0
Question by:davelane
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 2

LVL 37

Expert Comment

ID: 18757556

select case
when wkz > 0 then char(decimal(wkz, 12, 4))
else   "-"||char(decimal(abs(wkz, 12, 4)))
end
from lst.klk
where case
when wkz > 0 then char(decimal(wkz, 12, 4))
else   "-"||char(decimal(abs(wkz, 12, 4)))
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
0

LVL 50

Expert Comment

ID: 18758406
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

?
0

Author Comment

ID: 18760722
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?

0

Author Comment

ID: 18762710
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
0

Author Comment

ID: 18770056
Upping points
0

LVL 37

Accepted Solution

momi_sabag earned 2000 total points
ID: 18770450
Hi

i think i got a solution for you
assume that col1 is the column with the float value
the following computation will get rid of the leading zeroes
replace(ltrim(replace(char(decimal(col1,12,4)),'0',' ')),' ','0')
now you need to combine that with the previous solution of adding the minus sign :
select
case
when
wkz >= 0
then
replace(ltrim(replace(char(decimal(wkz,12,4)),'0',' ')),' ','0')
else
'-' || replace(ltrim(replace(char(decimal(abs(wkz),12,4)),'0',' ')),' ','0')
end

i tried it and it worked for me

momi

0

Author Comment

ID: 18773367
It's dirty, but I like it :)
0

## Featured Post

Question has a verified solution.

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

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
###### Suggested Courses
Course of the Month10 days, 19 hours left to enroll