Thread: Syntax

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

    Join Date
    Mar 2013
    Posts
    17
    Rep Power
    0

    Syntax


    can anyone tell exact syntax for TRIM in mysql.
  2. #2
  3. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,072
    Rep Power
    9398
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    17
    Rep Power
    0
    pls tell syntax for
    1)trim
    2)removing of tab characters in a particular coloumn
    2)removing of double quotes in a particular coloumn
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,179
    Rep Power
    4279
    Originally Posted by sajanmurugan
    pls tell syntax for
    1)trim
    2)removing of tab characters in a particular coloumn
    2)removing of double quotes in a particular coloumn
    the syntax for TRIM was given to you in post #2

    the syntax for removing tabs and quotes was given to you here
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    17
    Rep Power
    0

    White spaces


    i have a table name called location_master with three coloumns state ,city ,location.
    city coloumn contains white spaces in both left and right side of the string because of this i am facing many problems in my application.pls tell how to remove these white spaces.
    i used
    1.UPDATE `location_master` SET city = TRIM( city )
    2.UPDATE `location_master` SET `city` = LTRIM( `city` )
    3.UPDATE `location_master` SET `city` = rTRIM( `city` )
    all r showing zero rows affected..but i am damn sure still that spaces r ther.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    17
    Rep Power
    0

    City


    i have a coloumn called 'city' and it contains list of all cities.but the city called hyderabad contains three spaces to its front.i want replace that three spaces with@@@ ...table name is 'location_master' ..
    pls can anyone tell the syntax..
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,448
    Rep Power
    1751
    You've been pointed to the TRIM function a couple of times. In another post you have 3 examples of your usage which are all valid and do what they would suggest (trimming both ends, left end and right end).
    That would suggest that the whitespace there is not an actual space character but something akin to a tab character.

    Have a look at the ASCII() to get an idea of what the character is.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  14. #8
  15. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Location
    Iran
    Posts
    149
    Rep Power
    139
    Another possible solution would be REGEXP function, where you can check what cities include space/tabs at the beginning

    Code:
    mysql> SELECT TRIM(city)
        -> FROM
        -> (
        ->     SELECT '    city1'  AS city FROM DUAL UNION
        ->     SELECT 'city2'      AS city FROM DUAL UNION
        ->     SELECT 'city3'      AS city FROM DUAL UNION
        ->     SELECT ' city4'     AS city FROM DUAL      
        -> ) AS cities
        -> WHERE city REGEXP('^[ ]+');
    +------------+
    | TRIM(city) |
    +------------+
    | city1      |
    | city4      |
    +------------+
    2 rows in set (0.00 sec)
    
    mysql> 
    mysql> 
    mysql> 
    mysql> 
    mysql> REPLACE(city, '\t', '')
        -> FROM
        -> (
        ->     SELECT '\tcity1'    AS city FROM DUAL UNION
        ->     SELECT '\t\tcity2'  AS city FROM DUAL UNION
        ->     SELECT 'city3'      AS city FROM DUAL UNION
        ->     SELECT 'city4'      AS city FROM DUAL      
        -> ) AS cities
        -> WHERE city REGEXP('^[\t]+');
    +------------+
    | TRIM(city) |
    +------------+
    | city1      |
    | city2      |
    +------------+
    2 rows in set (0.00 sec)
    
    mysql>
    Regards,
    Dariyoosh
    Last edited by dariyoosh; March 12th, 2013 at 05:49 AM.
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    17
    Rep Power
    0
    thank you.but still i am facing same problem:-(
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,179
    Rep Power
    4279
    Originally Posted by sajanmurugan
    thank you.but still i am facing same problem:-(
    do you know how to dump your table? there are several ways to do it, including running the mysqldump utility or performing an export in a front-end application like phpmyadmin

    the dump will generate a CREATE TABLE statement, along with INSERT statements that will load the data

    post the CREATE TABLE statement, along with several of the INSERT statements that contain the problem data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo