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?
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.
You can use CASE statement.
WHEN SUBSTR(ColumnName,1,1) <> 'B'
AND SUBSTR(ColumnName,2,1) BETWEEN 1 AND 9
AND SUBSTR(ColumnName,3,1) BETWEEN 1 AND 9
WHEN SUBSTR(ColumnName,2,1) = 'A'
AND UPPER(ColumnName) <> LOWER(ColumnName) (CASESPECIFIC)
WHEN SUBSTR(ColumnName,3,3) = ''