How would I.... 
Author Message
 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.

Thanks.



Sat, 22 May 2004 06:25:33 GMT
 How would I....

Use some PL and let it throw an exception ...

DECLARE
 result    varchar2(x) ;
 num      number ;
BEGIN

 select to_number(substr(name,1,1)) into num from table ;
-- no exception thrown ? it's a number
 select 'LOT' || name into result from table ;

EXCEPTION
 WHEN ... THEN
-- exception handling -> the first character is a char
   select  name into result from table ;
END



Quote:
> 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.

> Thanks.



Sat, 22 May 2004 06:45:59 GMT
 How would I....
Got it.

SQL> select * from test51;

NAME
--------------------------------------------------
LOT
#56
94

  1  select
  2  case
  3    when (name between 'A' and 'Z')
  4     then 1
  5     else 0
  6  end result
  7* from test51
SQL> /

    RESULT
----------
         1
         0
         0

It's miller time

Quote:

> Use some PL and let it throw an exception ...

> DECLARE
>  result    varchar2(x) ;
>  num      number ;
> BEGIN

>  select to_number(substr(name,1,1)) into num from table ;
> -- no exception thrown ? it's a number
>  select 'LOT' || name into result from table ;

> EXCEPTION
>  WHEN ... THEN
> -- exception handling -> the first character is a char
>    select  name into result from table ;
> END



> > 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.

> > Thanks.



Sat, 22 May 2004 06:57:32 GMT
 How would I....

select case(when substr(name,1,1) between '0' and '9' then 'LOT '||name else
name)
from ...



Quote:
> 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.

> Thanks.



Sat, 22 May 2004 06:56:05 GMT
 How would I....

Quote:
> [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
  ;



Tue, 25 May 2004 07:24:10 GMT
 
 [ 5 post ] 

 Relevant Pages 

 
Powered by phpBB® Forum Software