RE: LIKE statement including wildcard and Ascii tab chr(9)

Try LIKE '%' || chr(9);

HTH, Yosi

> -----Original Message-----
> From: Linda Hagedorn [mailto:Linda_at_pets.com]
> Sent: Tuesday, August 01, 2000 4:54 PM
> To: Multiple recipients of list ORACLE-L
> Subject: LIKE statement including wildcard and Ascii tab chr(9)
>
>
> Hello,
>
> I'm looking for a LIKE statement on a varchar field where the
> last character
> in the field is a chr(9) ascii tab. I need to see the fields
> with regular
> alpha/numeric values which are ended with tab(s), and I can't find the
> correct syntax to code a LIKE '%"ASCII(9)"' or LIKE '%"chr(9)"'.
>
> I'm going to ask development to clean up this data, but I
> need to provide
> them with a consolidated list before I can proceed.
>
> I can see the row counts which contain tabs only with this syntax:
>
> SELECT distinct rawtohex(prod_id), COUNT(*) from
> bvadmin.bv_ext_prod_vis_history
> where prod_id NOT LIKE '%0%' and prod_id not like '%1%' and
> prod_id not like
> '%2%'
> and prod_id not like '%3%' and prod_id not like '%4%' and
> prod_id not like
> '%5%'
> and prod_id not like '%6%' and prod_id not like '%7%' and
> prod_id not like
> '%8%'
> and prod_id not like '%9%' and prod_id not like '%Q%' and
> prod_id not like
> '%P%'
> and prod_id not like '%K%' and prod_id not like '%U%'
> group by prod_id order by 2 desc ;
>
> RAWTOHEX(PROD_ID) COUNT(*)
> ---------------------------------------------- ---------
> 0909090909090909090909090909090909090909 9472
> 0909 5616
> 09 2017
>
> These are a sample of the rows I'm trying to retrieve with
> less than a full
> select on the table. I dumped most of the table to get this
> sample.
>
> RAWTOHEX(PROD_ID) COUNT(*)
> ---------------------------------------------- ---------
> 37363732383730303038333209 1
> 37363732383730303038313809 1
>
>
> 303731383539303034323733090909 2
> 303539343633303031313835090909 2
> 303539343633303031313233090909 2
>
> Any suggestions or examples of a wildcard search for chr(9) tab are
> appreciated.
>
> Thanks, Linda
> --
> Author: Linda Hagedorn
> INET: Linda_at_pets.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Tue Aug 01 2000 - 16:48:48 CDT