#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    7
    Rep Power
    0

    Trim leading 0 on macthing rows


    Hi guys

    I want to remove a leading 0 from all rows that math '00.%'
    COL1
    0123
    1222
    0012
    0018

    I would like the above to be
    COL1
    0123
    1222
    012
    0018

    I have tried this
    Code:
    update table set telnum = trim(leading '0' from telnum where telnum like '00%')
    and get this error
    ERROR: syntax error at or near "where"
    Last edited by bboy154; July 25th, 2013 at 05:04 AM. Reason: Typo
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    Originally Posted by bboy154
    and get this error
    ERROR: syntax error at or near "where"
    Take a close look at the parentheses.
    The 6 worst sins of security How to (properly) access a MySQL database with PHP

    Why cant I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    7
    Rep Power
    0
    Originally Posted by Jacques1
    Hi,



    Take a close look at the parentheses.
    the parentheses was a typo please see now.
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    It's still the parentheses. There's no such thing as

    Code:
    trim(leading '0' from telnum where telnum like '00%')
    I guess you mean

    sql Code:
    UPDATE
    	some_table
    SET
    	telnum = TRIM(LEADING '0' FROM telnum)
    WHERE
    	telnum LIKE '00%'
    ;

    You should start formatting your queries, because this will make syntax errors like this one much easier to see.
    The 6 worst sins of security How to (properly) access a MySQL database with PHP

    Why cant I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    7
    Rep Power
    0
    Thanks Jacques1, I am new to SQL, but I will take you excellent advise about formatting

IMN logo majestic logo threadwatch logo seochat tools logo