Please let us know, what exactly you want to acheive with some examples of input and desired output.
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.
Can anyone help me on this?
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
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?
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
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) = ''
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
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
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)
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
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?