like operator in select statement

[EKC]

Hi ,

Select field1,field2 from Table1 where field1 like "A%" gives me all records in field1 that start with A
Field2 is number.If I want all numbers starting with 9 statement like "9%" doesn't work.
How to select all records(field2)that starts with 9?
Thanks

 

[BlindPete]

assuming the field is numeric a value...
WHERE LEFT(STR(Field1),1)="9"
or
WHERE ROUND(Field1,0) = 9 &&depends on numeric format
or
WHERE ROUND(Field1,2) = 0.09 &&depends on numeric format

if it is a character field already drop the STR() funtion.

-Pete

 

[jonscott8]

Since you appear to be fond of the LIKE operator:

WHERE TRANSFORM(field2) LIKE '9%'

If you anticipate your table having many records, you might want to consider creating an index on TRANSFORM(field2). Jon Hawkins

The World Is Headed For Mutiny,
When All We Want Is Unity. - Creed

 

[Walid]

Hi my friend,
Try this

*// Name is your string field, number is numeric field
lcForCond=[LIKE("9*",ALLT(STR(number)))]
SELECT name,number FROM table1 WHERE name LIKE "A%" AND &lcForCond

Thanks, Walid Magd
Engwam@Hotmail.com

 

[Walid]

I don't know why my for condition look like this after I post it, anyways it is
lcForCond=[LIKE("9*",ALLT(STR(number)))]
Walid Magd
Engwam@Hotmail.com