Counting the number of occurrences of a character within a character string

Analytics
Teradata Employee

Counting the number of occurrences of a character within a character string

Hello!

Would anyone have an easy way of counting the occurrences of a character within a character string?

So, if the string were AXFDXHAYSXDUSX you would get the result 2 for A, 4 for X, 1 for F, 2 for D and so on?

I cannot think of any defined functions that would do this. Perhaps there is a UDF or other workaround using a combination of functions?

Many thanks!!!

Andrew Livingston
3 REPLIES
Teradata Employee

Re: Counting the number of occurrences of a character within a character string

Three ways I can think of:

1. Write an UDF to evaluate

2. Write a SP to count them like you would in UDF

3. Use recursive SQL to iterate through each character and insert the character into a table. Then count the number of characters as select mychar, count(*) from foo group by 1;
Teradata Employee

Re: Counting the number of occurrences of a character within a character string

UDFs will be the easiest, most usable and most efficient way!

Regards,

Adeel
Teradata Employee

Re: Counting the number of occurrences of a character within a character string

Adeel/Jeff_O,

Thanks for your replies. I was thinking along the same line, but was hoping for some little known function in SQL. SQL isn't the best for string manipulation, but I suppose it is more meant for data manipulation!

Thanks!

Andrew