Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433

    Unanswered: no of occurrence of ',' in a string

    i want to count no of occurrence of ',' in a string.
    Is there any function to get that
    unable to search any such function.
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    does DB2 have a REPLACE function?

    i'm too lazy to look it up on the web and i'm guessing you have a manual handy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Rahul,
    There is no built in function to count a character in a string. You can write a UDF to do it though.

    Andy

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    r937,
    Please start a new thead when asking a new question. DB2 does have a REPLACE function. Look in the Reference manual for particulars.

    Andy

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh please

    as a moderator, i am quite familiar with the problem of people posting new questions in some unrelated thread, and i assure you, i would never do that

    SELECT LENGTH(yourstring)
    - LENGTH(REPLACE(yourstring,',','')) as number_of_commas
    FROM ...

    see? no UDF needed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by r937
    oh please

    as a moderator, i am quite familiar with the problem of people posting new questions in some unrelated thread, and i assure you, i would never do that

    SELECT LENGTH(yourstring)
    - LENGTH(REPLACE(yourstring,',','')) as number_of_commas
    FROM ...

    see? no UDF needed
    Well excuse me. Your original post looks like an unrelated question. Maybe if you would have posted the above solution along with the question, it would have been more obvious. If this is just a one time request, then no UDF is necessary, but if it needs to be used frequently, I would still make it a UDF.

    Andy

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that would have required me to look up stuff in the manual, and i don't always feel like doing that

    and of course i would look pretty silly posting a solution that doesn't work

    REPLACE hasn't always been supported in DB2, which is why i asked -- i'm sure you know there are many different versions of DB2, and by asking the original poster to do the work of looking it up, i was simply trying to determine whether my solution had any chance of working

    sorry to have caused you such concern
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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