Is there method in teradata to find number of occurence of a specific character in a string?
Like if string value = ' Teradata is Relational Database'
I need to know how many time letter 'a' is present in the above string.
Do we have any inbuilt functions in Teradata or will Regular expression help me on this?
You can try the below:
Sel character_length('Teradata is Relational Database') - character_length(Oreplace('Teradata is Relational Database','a',''));
-- will give you 8.
If you have oTranslate / oReplace UDFs then its very easy; Otherwise you have to write your own.
sel CHAR_LENGTH('Teradata is Relational Database') - CHAR_LENGTH(OTRANSLATE('Teradata is Relational Database', 'a',''));
· Get the original length of the string -L1 -> char_length('Function')
· Replace say 'n' by space -R1 -> oreplace('Function' ,'n','')
· Calculate again length of R1 - L2 -> char_length(oreplace('Function' ,'n',''))
· L1-L2 gives u the occurrences
E.g -In the word 'Function' you want to check how many 'n' is present
sel (char_length('Function') - char_length(oreplace('Function' ,'n','')));
sel (char_length('Function') - char_length(otranslate('Function' ,'n','')));
Thanks Ranga & Sakthi, your answers really help, but is there a funtion or regexp we can identify this in single go?