SQL phrase required for beginning and end of field

[ozgirl]

Hello,
I need to search on our bouquet code field for everything beginning with W and ending with 2, examples of what may be in the field are:WWH2, WTAB2, WNUTPAC2 (ie: varying lengths)

Can you please provide me with the sql so I can include it in my following program:

USE fforder
REPLACE ALL RECALL WITH {28.11.2003} FOR fforder.delivery >= {22.12.2003};
AND fforder.delivery <= {02.01.2004};
AND fforder.desp_ctry = &quot;0&quot;;
AND fforder.bqt_type = [?????code requested];

Looking forward to hearing back - I know it's something simple but I don't know what it is??
Thank you
Katie

 

[jimoo]

Here is the select command. It should provide you with the info you're looking for.

SELECT field1, field2 FROM tablename WHERE LEFT(fieldname,1) = &quot;W&quot; AND RIGHT(ALLTRIM(fieldname),1) = &quot;2&quot;

Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[ozgirl]

Thanks heaps - it works!!
Katie

 

[ozgirl]

Sorry - first part worked - now I need to update this selection by replacing a field with another date and it's saying cannot update the cursor


USE fforder
SELECT order_no, recall, bqt_type FROM fforder WHERE LEFT(bqt_type,1) = &quot;W&quot; AND RIGHT(ALLTRIM(bqt_type),1) = &quot;2&quot;;
and delivery={25.12.2003}

REPLACE RECALL WITH {28.11.2003}

Many thanks again
Katie

 

[jimoo]

My 1st post probably confused you. The SELECT statement sends the data, by default, to a read-only cursor.

REPLACE ALL RECALL WITH {28.11.2003} FOR fforder.delivery >= {22.12.2003};
AND fforder.delivery <= {02.01.2004};
AND fforder.desp_ctry = &quot;0&quot;;
AND fforder.bqt_type = [?????code requested];
AND LEFT(fieldname,1) = &quot;W&quot; AND RIGHT(ALLTRIM(fieldname),1) = &quot;2&quot;



Again, change fieldname to your &quot;real&quot; fieldname.

Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[BPeisch]

Katie,

Assuming you're send your results into a cursor, you need to add a READWRITE clause to the end of your SQL command.

SELECT field1, field2 FROM tablename WHERE LEFT(fieldname,1) = "W" AND RIGHT(ALLTRIM(fieldname),1) = "2" into cursor MyCursor READWRITE

-BP

http://www.peisch.com