Method to find number of occurence of specific character in a string

Database
Enthusiast

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?

4 REPLIES
Enthusiast

Re: Method to find number of occurence of specific character in a string

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

Enthusiast

Re: Method to find number of occurence of specific character in a string

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

Enthusiast

Re: Method to find number of occurence of specific character in a string

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

Enthusiast

Re: Method to find number of occurence of specific character in a string

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