Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2011
    Posts
    8

    Unanswered: DB2 String function

    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

  2. #2
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    = length(string) - length(replace(string,','.''))
    Dick Brenholtz, Ami in Deutschland

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 09-23-11 at 06:32. Reason: Add notes "Sorry, Dick is right. I forgot ...."

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.

  5. #5
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    Quote Originally Posted by n_i View Post
    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.
    Dick Brenholtz, Ami in Deutschland

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •