Re: How would I....

From: Nicholas Carey <ncarey_at_speakeasy.org>
Date: Thu, 06 Dec 2001 23:24:10 -0000
Message-ID: <Xns916F9CADD20B4ncareyspeakeasyorg@207.126.101.92>

On 03 Dec 2001, Jon Bon Jovi <jon_at_bon.com> spake and said:

> [how would i] Take the first letter of a varchar2 column and
> determine if it's a char or a number? ie.
>
> select decode(substr(name,1,1),<IF STRING>,name,'LOT
> '||name) from table
>
> If the first char of the name column is a string then just
> use the name column, otherwise concatenate the word 'LOT ' to
> the beginning of the column.

Other suggestions comparing against a character range will only work for ASCII character sets. They won't work for EBCDIC (IBM mainframe type boxes.) The correct and portable way is something along these lines:

  select decode( translate(substr(name,1,1),

                   '0123456789' ||
                   'ABCDEFGHIJKLMNOPQRSTUVWXYZ' ||
                   'abcdefghijklmnopqrstuvwxyz' ,
                   'NNNNNNNNNN' ||
                   'AAAAAAAAAAAAAAAAAAAAAAAAAA' ||
                   'AAAAAAAAAAAAAAAAAAAAAAAAAA'
                   ) ,

                   'A' , 'ALPHA:' , -- alpha character [a-z]
                   'N' , 'DIGIT:' , -- decimal digit   [0-9]
                         'OTHER:' , -- other
               ) || t.name character_class

  from my_table t
  ; Received on Thu Dec 06 2001 - 17:24:10 CST