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

ahmed2502 11 posts Joined 12/12
11 Jul 2014

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

ravimans 54 posts Joined 02/14
11 Jul 2014

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.
 

ravimans 54 posts Joined 02/14
13 Jul 2014

Can anyone help me on this?

dnoeth 4628 posts Joined 11/04
14 Jul 2014

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

 

Dieter

ravimans 54 posts Joined 02/14
16 Jul 2014

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?

dnoeth 4628 posts Joined 11/04
16 Jul 2014

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'

Dieter

ravimans 54 posts Joined 02/14
19 Jul 2014

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) = ''

dnoeth 4628 posts Joined 11/04
20 Jul 2014

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'

Dieter

yuvaevergreen 93 posts Joined 07/09
22 Jul 2014

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
 

09 Aug 2014

Can you plase check below code.
 
sel
'abc def' as str1
,'abcdef' as str2
,char(str1)
,char(oreplace(str1,' ',''))
,char(str2)
,case when char(str1)<>char(oreplace(str1,' ','')) then 'SPACE IS PRESENT' ELSE 'No SPACE' END
,POSITION( ' ' IN  str1)
,POSITION( ' ' IN  str2)

 

09 Aug 2014

PFB ::
sel
'abc def' as str1
,'abcdef' as str2
,char(str1)
,char(oreplace(str1,' ',''))
,char(str2)
,case when char(str1)<>char(oreplace(str1,' ','')) then 'SPACE IS PRESENT' ELSE 'No SPACE' END
,POSITION( ' ' IN  str1)
,POSITION( ' ' IN  str2)

;

 *** Query completed. One row found. 8 columns returned.
 *** Total elapsed time was 1 second.

str1     str2    Characters(str1)  Characters(oreplace(str1,' ',''))  Characters(str2)  <CASE  expression>  Position(' ' in str1)  Position(' ' in str2)
-------  ------  ----------------  ---------------------------------  ----------------  ------------------  ---------------------  ---------------------
abc def  abcdef                 7                                  6                 6  SPACE IS PRESENT                        4                      0
 

You must sign in to leave a comment.