SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Removing commas at front of entry

    A very kind gentleman on sitepoint helped me last week to put together a sql statement for some keywords.

    I'm afraid I'm back again after multiple tries in figuring this out for myself.

    table: content
    I have a field "metakey"

    that displays like this:

    , dog, cat, fish,
    Can anyone help with a sql command which will remove the space and comma at the start of these rows (it's not present on all rows so would probably need to include a WHERE variable?)

    result should be:

    dog, cat, fish,
    Any help would be greatly appreciated.

  2. #2
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Something like this maybe:

    SELECT SUBSTRING(metakey, 2, (LEN(metakey) - 2)) AS metakey FROM content WHERE metakey LIKE ', %';

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    TRIM(LEADING ',' FROM metakey)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    TRIM(LEADING ',' FROM metakey)
    You learn something new every day

  5. #5
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Guys thanks so much for your help.

    I am struggling with this it seems though so some further advice would be fantastic.

    SJH I receive a database error:
    FUNCTION database_name.LEN does not exist
    When trying the query.

    R937 I've tried these:
    SELECT TRIM(LEADING ', ' FROM metakey) FROM content
    SELECT TRIM(LEADING ',' FROM metakey) FROM content
    Whilst phpmyadmin doesn't return an error I have 0-29 rows show as results but the content alterations don't seem to of taken place.

    Any ideas?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jasper1106 View Post
    ...but the content alterations don't seem to of taken place.
    i certainly hope not!!!

    there would be millions of mysql users extremely upset if word ever leaked out that a SELECT statement sometimes changes data...

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

  7. #7
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Apologies for my newbness I am really guessing here trying to get a couple of tasks done - I've tried removing the Select but then get an error that the syntax isn't correct?

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,510
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    I'm sure the kind gentleman last week already told you that putting multiple values in a single column is asking for trouble? So I won't start about that again

    If you want to change data in your table, then you'll have to use UPDATE: http://dev.mysql.com/doc/refman/5.1/en/update.html

  9. #9
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Guido thanks for your reply. Originally I tried UPDATE but it said error with the syntax the select one was the only one that didn't return an error but it's doing the wrong thing obviously.

  10. #10
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,510
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Post your UPDATE query and the exact error it gives.

  11. #11
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    UPDATE TRIM(LEADING ',' FROM metakey) FROM content
    The error i'm receiving is:

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRIM(LEADING ',' FROM metakey) FROM content' at line 1
    Any ideas?

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you should really take the time to learn the basic SQL statements

    the UPDATE statement starts with the keyword UPDATE

    after that comes the name of the table you're updating!!!!

    every basic SQL tutorial covers this stuff
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the advice as always. Wish it would give a better error description as it's really difficult to learn otherwise.

    I'm still getting errors, what am i doing wrong?

    UPDATE content TRIM(LEADING ',' FROM metakey)

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jasper1106 View Post
    what am i doing wrong?
    what you are doing wrong is not taking the time to go through a basic SQL tutorial to find out the syntax of the UPDATE statement

    you might also read the fine manual...

    UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT row_count]

    if you remove the optional stuff, this condenses down to --

    UPDATE tbl_name
    SET col_name1=expr1

    does this give you more of a clue?

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

  15. #15
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks R, think im going to have to look for a developer in SQL as this is double dutch to me. :S


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
  •