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.
davelaneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
so how about

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
LowfatspreadCommented:
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
davelaneAuthor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

davelaneAuthor Commented:
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
davelaneAuthor Commented:
Upping points
0
momi_sabagCommented:
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
hope this solves your problem

momi


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
davelaneAuthor Commented:
It's dirty, but I like it :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.