query help! please. 2

[jcisco]

Can someone please help me with this query.
I have a table that holds Reporting data for items sold.
Fields in this table are like CompanyCode, InvoiceDate, CustomerNum, ItemNum, etc..
Now my question is this. I need to write a query that filters on the itemnumbers.

An example of this would be:
(----data held in the table---)
11503
1132558CC
PROMO
BILLBACK
15565 Wild O

And i need a query that pulls only the itemNumbers that Have digits in them, or Digits followed by CC (Any other char's other than CC is an invald ItemNumber). I can have up to a 10 digit number.

So after the query, I should have only the following values:
11503
1132558CC

is there any way to pull this off? Please please please help.

cheers
john

(Any questions please ask, i will try to clear up any question.)

--------------

 

[zhavic]

Try this:

SELECT * FROM your_table
WHERE itemNumber LIKE ( REPLICATE( '[0-9]', LEN( RTRIM( LTRIM( itemNumber ) ) ) ) )
OR itemNumber LIKE ( REPLICATE( '[0-9]', LEN( RTRIM( LTRIM( itemNumber ) ) ) - 2 ) + 'CC' )


Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[nigelrivett]

WHERE patindex('%[^0-9]%',itemNumber) = 0
or (patindex('%[^0-9]%',left(itemNumber,len(itemNumber-2)) = 0 and right(itemNumber,2) = 'CC')


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[jcisco]

THANK YOU!

--------------