Position value for numeric, alphabets and blank

Database
Enthusiast

Position value for numeric, alphabets and blank

I am having one column where it can have position 3-5 as blank. It can be as position 3 as blank or position 4-5 as blank.

I tried using substr(colname, 5, 1) = ' ' but it's not working correctly. Any suggestions? Also i am checking isnumeric as between '0000' and '9999'. Then alphabets as betweem 'A' and 'Z'. Please let me know is this correct or not?

For ex:

Input could be column A where it has value as A1234D, HGDF34 etc..

I need to get output with some case stmt return values..

For ex: If Position 1 is not B and Position 2-4 is numeric THEN 'ABC'

If Position 2 is A and Position 3-5 is alpha THEN 'DEF'

If Position 3-5 is blank THEN 'GHI.

1 REPLY
Enthusiast

Re: Position value for numeric, alphabets and blank

You can use CASE statement.

CASE 

WHEN SUBSTR(ColumnName,1,1) <> 'B'

AND SUBSTR(ColumnName,2,1) BETWEEN 1 AND 9

AND SUBSTR(ColumnName,3,1) BETWEEN 1 AND 9

THEN 'ABC'

WHEN SUBSTR(ColumnName,2,1) = 'A'

AND UPPER(ColumnName) <> LOWER(ColumnName) (CASESPECIFIC)

THEN 'DEF'

WHEN SUBSTR(ColumnName,3,3) = ''

THEN 'GHI'

ELSE

'' END