All Forums Database
ravimans 54 posts Joined 02/14
14 Jul 2014
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.
 

ahmed2502 11 posts Joined 12/12
15 Jul 2014

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

 

You must sign in to leave a comment.