Blank space check based on the position

Database
Enthusiast

Blank space check based on the position

Hi,

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?

11 REPLIES
Enthusiast

Re: Blank space check based on the position

Please let us know, what exactly you want to acheive with some examples of input and desired output.

Enthusiast

Re: Blank space check based on the position

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.

Hope this clarifies your question. Let me know if you need any other details.

Enthusiast

Re: Blank space check based on the position

Can anyone help me on this?

Senior Apprentice

Re: Blank space check based on the position

What's your TD release?

In TD14 there are Regular Expression:

CASE WHEN regexp_similar(col, '[^B]([0-9]){3}.*') = 1 THEN 'ABC'
...

otherwise you need to SUBSTRING each character and check for it's value

CASE WHEN SUBSTRING(col FROM 1 FOR 1) <> 'B'
AND SUBSTRING(col FROM 2 FOR 1) BETWEEN '0' AND '9'
AND SUBSTRING(col FROM 3 FOR 1) BETWEEN '0' AND '9'
AND SUBSTRING(col FROM 4 FOR 1) BETWEEN '0' AND '9'
THEN 'ABC'
......
Enthusiast

Re: Blank space check based on the position

Thanks Dieter for the details.

I used substring to acheive the result but it says "SELECT Failed. 2621: Bad character in format or data". The column which i am checking has a datatype of VARCHAR(7) and i used to convert it to numeric when i am checking for numeric values. For ex: 

WHEN CAST(SUBSTRING(col,1,3) AS NUMERIC) BETWEEN '0' AND '99999' AND SUBSTRING(col,4,1) BETWEEN 'A' AND 'Z' THEN 'AAA'

WHEN (CAST(SUBSTRING(col,1,2) AS NUMERIC) BETWEEN '0' AND '99999' AND SUBSTRING(col,3,1) = '') THEN 'CCC'

WHEN SUBSTRING(col,1,1) = 'B' AND CAST(SUBSTRING(col,2,4) AS NUMERIC) BETWEEN '0' AND '99999'  THEN 'BBB'

One wired thing is for some of the alphabet character it works correctly but few of them it shows error as given above. Anything wrong in my query?

Senior Apprentice

Re: Blank space check based on the position

Of course casting to a numeric value will fail if there are non-numeric characters in your string.

If you can't use REGEX_SIMILAR you need to check each character if it's between '0' and '9'

Enthusiast

Re: Blank space check based on the position

But when i check for the NULL/BLANK space values using '', it's not getting calculated correctly. I am not sure whether it's because of the substring value i am checking NULL or since it's VARCHAR field, do we need to convert it to NUMERIC anf check for NULL values? Even i used TRIM function also. None of them is calculating the NULL value using the substring.

SUBSTRING(Col FROM 2 FOR 1) BETWEEN '0' AND '9' AND SUBSTRING(UMSV.IPCD_ID FROM 3 FOR 1) = ''

Senior Apprentice

Re: Blank space check based on the position

Can you show exactly what you tried?

Of course this compares to spaces as expected. If not, the "blanks" might only look like spaces but are something different. 

Can you check wit.h a CHAR2HEXINT(col) the spaces should be hex '20'

Enthusiast

Re: Blank space check based on the position

IF the no of characters to be validated for numeric is less, then you can use "IN" option. But, not good for more no of columns or alphanumeric...Best option would be to go for UDF in TD13..

CASE WHEN SUBSTR(COL,1,1) IN ('1','2','3','4','5','6')

     THEN 'ABC'

END