DB2 String function

kayal · #1 (**permalink**) 09-23-11, 05:08

Hi,

I want to get the function to count the number of occurance of a particular character in a string.
Ex. In the string 'xx, yy, zz', I want to get the number of ','s(Commas).

Thanks,
Kayal

dbzTHEdinosaur · #2 (**permalink**) 09-23-11, 05:24

= length(string) - length(replace(string,','.''))

tonkuma · #3 (**permalink**) 09-23-11, 05:27

Try
( LENGTH('xx, yy, zz') - LENGTH( REPLACE('xx, yy, zz' , ',' , '') ) ) / LENGTH(',')

Sorry,
Dick is right.

I forgot OP wrote "a particular character", not "a particular string".
So "/ LENGTH(',')" is not necessary.

n_i · #4 (**permalink**) 09-23-11, 08:12

Actually, a particular character could be more than 1 byte long in a Unicode database, while LENGTH() returns the number of bytes, so I would use Tonkuma's suggestion to be on the safe side.

dbzTHEdinosaur · #5 (**permalink**) 09-23-11, 09:09

Quote:

Originally Posted by n_i

Actually, a particular character could be more than 1 byte long in a Unicode database, while LENGTH() returns the number of bytes, so I would use Tonkuma's suggestion to be on the safe side.

we are looking for occurances,
so good point and accurate.

being from an ebcdic only, mainframe environment, I forget that there are other worlds out there.

Internet Services

Web Development

Digital Marketing

Consumer Tech