is it possible?

[iffoiffy]

Hi,

Suppose I have a varchar field "A" in my table that has a value '120 g'
I have a variable $weight=120;

Now I want to run a query which compares $weight to 120 of field A

Can I do that? I think I will have to extract the number from 120 G and then do the comparsion.or something like that/........

Thanks

 

[r937]

the first thing to do is to find the space and assume that everything to the left of it is numeric

this works for values like '120 g'

but you have to be careful, because perhaps there might be values of A which don't have a space, like '120g'

so you need to look for the first non-numeric

even then you're not safe, because what if there's a value of A like '120' -- then any search for a space or non-numeric will return 0 (not found)

you could use several CASE evaluations, such as

CASE WHEN instr(A,' ') > 0
THEN left(A,instr(A,' ')-1)
WHEN instr(A,'g') > 0
THEN ...

but this could get tedious and you might miss a pattern -- for instance, a value like '3 kg' would need special handling

no, the real answer is: DON'T DO THIS AT ALL!!!

go back to your table definition and redefine how to handle weights as a combination of two columns -- a numeric value and some sort of unit code (grams, kilograms, whatever)




rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)