SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Count the number of entries seperated by a comma

    hello, how do i Count the number of entries seperated by a comma if i have certain number of records inserted into my database

    e.g.

    how do i count


    hello,hello,hello, hello

    1. I would like to know how many hello the user has posted after each comma
    2. The lenght of each entry with the LEN function

    thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    first of all, i need to give you the standard warning that any time you store a list inside a column, you probably have a very poor design (if you intend to search or join on the entries in the list)

    the way to count the entries is to replace all commas with zero-length strings, subtract the length of the result from the length of the original string, add 1, and voilą
    Code:
    select len(colname)
          -len(replace(colname,',','') + 1 
                as number_of_items...
    as far as getting the length of each entry in the list, see standard warning

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Amazing Rudy, appears you are a jack of all trades and master of All

    :-)

    how do i implement this in the code am not conversant with Jscript

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Jscript? that's SQL, my friend

    you posted this in the Databases forum

    methinks you should really cut back on the caffeine...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally Posted by r937methinks you should really cut back on the caffeine...
    No caffeine here sir?...
    dont drink that stuff,

    But you are kind of right, i posted a similar comment in javascript section.

    I think i have had enough for 2day.

    See u tomorrow
    Afrika

  6. #6
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh,
    no list is stored in the column, I have a phone book for my web 2phone sms, and a user could choose to bulk blast sms to many users in the phone book, and we keep records of what they blast in the history table, for future references.

    thanks for you assistnace and continued :-) advice

    rgds


Bookmarks

Posting Permissions

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