Solved

Searching Float columns with LIKE - Float to string cast

Posted on 2007-03-20
7
859 Views
Last Modified: 2010-05-18
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
Comment
Question by:davelane
  • 4
  • 2
7 Comments
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
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
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
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

by:davelane
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:davelane
Comment Utility
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

by:davelane
Comment Utility
Upping points
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
Comment Utility
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
 

Author Comment

by:davelane
Comment Utility
It's dirty, but I like it :)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now