SQL SELECT and exact values

I have a table with a field (ITEM) that contains the values "ITEM1"
through "ITEM72."
-
The SQL SELECT statement below does NOT include the values "ITEM10",
"ITEM11", etc., because these values begin with "ITEM1..."
-
SELECT * ;
FROM mytable;
WHERE .NOT. INLIST(item,'ITEM1')
-
How could I alter the SELECT statement to eliminate "ITEM1," but keep
"ITEM10," "ITEM11," etc., in the results?
-
Many thanks,
David

John
Fri Jan 27 11:54:02 CST 2006

SELECT * from tablename WHERE LEN(RTRIM(tablename.item))>5
How this helps.
John.



"David" <David.Aman@dpsnc.net> wrote in message
news:1138381156.792654.277820@o13g2000cwo.googlegroups.com...
> I have a table with a field (ITEM) that contains the values "ITEM1"
> through "ITEM72."
> -
> The SQL SELECT statement below does NOT include the values "ITEM10",
> "ITEM11", etc., because these values begin with "ITEM1..."
> -
> SELECT * ;
> FROM mytable;
> WHERE .NOT. INLIST(item,'ITEM1')
> -
> How could I alter the SELECT statement to eliminate "ITEM1," but keep
> "ITEM10," "ITEM11," etc., in the results?
> -
> Many thanks,
> David

David
Fri Jan 27 11:56:07 CST 2006

My second sentence should have read:
-
The results from the SQL SELECT statement below do NOT include the
values "ITEM10", "ITEM11", etc., because these values begin with
"ITEM1..."
-
(It's difficult to write something so simple...)

Olaf
Fri Jan 27 12:21:00 CST 2006

> How could I alter the SELECT statement to eliminate "ITEM1," but keep
> "ITEM10," "ITEM11," etc., in the results?

SELECT * ;
FROM mytable;
WHERE .NOT. INLIST(item,'ITEM1 ')

Bye, Olaf.

Cindy
Fri Jan 27 12:29:09 CST 2006

Hi David,

Read about Set Ansi in Help.

Create Cursor Test (Field1 C(10))
Insert Into Test Values ("Item1")
Insert Into Test Values ("Item11")
Set Ansi Off
Select * From Test Where Not InList(Field1, "Item1")
Set Ansi On
Select * From Test Where Not InList(Field1, "Item1")


--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn.com www.cindywinegarden.com


"David" <David.Aman@dpsnc.net> wrote in message
news:1138381156.792654.277820@o13g2000cwo.googlegroups.com...
>I have a table with a field (ITEM) that contains the values "ITEM1"
> through "ITEM72."
> -
> The SQL SELECT statement below does NOT include the values "ITEM10",
> "ITEM11", etc., because these values begin with "ITEM1..."
> -
> SELECT * ;
> FROM mytable;
> WHERE .NOT. INLIST(item,'ITEM1')
> -
> How could I alter the SELECT statement to eliminate "ITEM1," but keep
> "ITEM10," "ITEM11," etc., in the results?
> -
> Many thanks,
> David
>

JohnnyCinco
Fri Jan 27 12:36:51 CST 2006

David oh buddy.

I have come accross this before so I will do my best to help.

STORE 'ITEM1' to ItemOne

SELECT * FROM 'Table' WHERE item != ?ItemOne

Try this.

JohnnyCinco


"David" <David.Aman@dpsnc.net> wrote in message
news:1138381156.792654.277820@o13g2000cwo.googlegroups.com...
>I have a table with a field (ITEM) that contains the values "ITEM1"
> through "ITEM72."
> -
> The SQL SELECT statement below does NOT include the values "ITEM10",
> "ITEM11", etc., because these values begin with "ITEM1..."
> -
> SELECT * ;
> FROM mytable;
> WHERE .NOT. INLIST(item,'ITEM1')
> -
> How could I alter the SELECT statement to eliminate "ITEM1," but keep
> "ITEM10," "ITEM11," etc., in the results?
> -
> Many thanks,
> David
>

Bernhard
Fri Jan 27 12:38:39 CST 2006

Hi David

> I have a table with a field (ITEM) that contains the values "ITEM1"
> through "ITEM72."
> -
> The SQL SELECT statement below does NOT include the values "ITEM10",
> "ITEM11", etc., because these values begin with "ITEM1..."
> -
> SELECT * ;
> FROM mytable;
> WHERE .NOT. INLIST(item,'ITEM1')
> -
> How could I alter the SELECT statement to eliminate "ITEM1," but keep
> "ITEM10," "ITEM11," etc., in the results?

If you would like to rely on numeric order and your pre text always is of length
4, then maybe you try this:

SELECT *;
FROM mytable;
WHERE val(substr(item, 5)) >= 10

Regards
Bernhard Sander

Dave
Fri Jan 27 21:49:41 CST 2006

David wrote:
> I have a table with a field (ITEM) that contains the values "ITEM1"
> through "ITEM72."
> -
> The SQL SELECT statement below does NOT include the values "ITEM10",
> "ITEM11", etc., because these values begin with "ITEM1..."
> -
> SELECT * ;
> FROM mytable;
> WHERE .NOT. INLIST(item,'ITEM1')
> -
> How could I alter the SELECT statement to eliminate "ITEM1," but keep
> "ITEM10," "ITEM11," etc., in the results?
> -
> Many thanks,
> David
>
Select * From mytable Where !item== "Item1"

Dave Tiffany