All Forums Database
kirthi 65 posts Joined 02/12
28 Aug 2015
Method to find number of occurence of specific character in a string

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?
 

28 Aug 2015

Hi,
You can try the below:
Sel character_length('Teradata is Relational Database') - character_length(Oreplace('Teradata is Relational Database','a',''));
-- will give you 8.
Thank you!
Ranga
 

sakthikrr 53 posts Joined 07/12
30 Aug 2015

Hi Kirthi,
 
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',''));

Thanks!
- Sakthi

Sakthi

sakthikrr 53 posts Joined 07/12
30 Aug 2015

In detail,
 
·         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','')));
 
- Sakthi

Sakthi

kirthi 65 posts Joined 02/12
31 Aug 2015

Thanks Ranga & Sakthi, your answers really help, but is there a funtion or regexp we can identify this in single go?

You must sign in to leave a comment.