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

    Join Date
    Mar 2013
    Posts
    17
    Rep Power
    0

    Tab characters


    i have a table name called my_table which has 3 coloumns namely user_id,profile_key and profile value.
    problem is 'profile value' contains some tab characters(\t) in the begginnig and end of the string.
    like this-> \tshimla\t now i want to remove the '\t' .

    pls help
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Code:
    UPDATE my_table
       SET profile_value = REPLACE(profile_value,CHAR(09),'')
     WHERE profile_value LIKE CONCAT('%',CHAR(09),'%')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    17
    Rep Power
    0
    will it affect on other data???? becuase that table contains very imp data...
    thank u
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    17
    Rep Power
    0
    i got result as zero rows affected. but i am sure my data contains tab characters.
    and also tell the syntax for removing double quotes.


    regrds
    sajan
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by sajanmurugan
    ...also tell the syntax for removing double quotes.
    it's exactly the same, except the REPLACE function replaces a different character
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    17
    Rep Power
    0

    Removing Tab Characters


    i have a table name called my_table which has 3 coloumns namely user_id,profile_key and profile value.
    problem is 'profile value' contains some tab characters(\t) in the begginnig and end of the string.
    like this-> \tshimla\t now i want to remove the '\t' .
    I USED THE FOLLOWING BUT ITS NOT WORKING

    UPDATE change_user_profiles
    SET profile_value = REPLACE(profile_value,CHAR(09),'')
    WHERE profile_value LIKE CONCAT('%',CHAR(09),'%')

    ITS SHOWING ZERO ROWS AFFECTED :-(
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Location
    Iran
    Posts
    149
    Rep Power
    140
    As you didn't provide a complete test case I just create a temporary table (non normalized) as an example

    Code:
    DROP TABLE test_table;
    CREATE TABLE test_table
    (
        user_id         VARCHAR(30),
        profile_key     VARCHAR(30),
        profile_value   VARCHAR(30)
    );
    
    INSERT INTO test_table(user_id, profile_key, profile_value)
        VALUES  ('usr-id-00001', 'profile_key-00001', 'profile_value-00001'),
                ('usr-id-00002', 'profile_key-00002', '\tprofile_value-00002\t'),
                ('usr-id-00003', 'profile_key-00003', 'profile_value-00003'),
                ('usr-id-00004', 'profile_key-00004', '\tprofile_value-00004\t'),
                ('usr-id-00005', 'profile_key-00005', 'profile_value-00005');
                
    SELECT * FROM test_table;
    The output as expected includes tabulations for profile_value
    Code:
    Query OK, 0 rows affected (0.06 sec)
    
    Query OK, 0 rows affected (0.12 sec)
    
    Query OK, 5 rows affected (0.05 sec)
    Records: 5  Duplicates: 0  Warnings: 0
    
    +--------------+-------------------+-----------------------+
    | user_id      | profile_key       | profile_value         |
    +--------------+-------------------+-----------------------+
    | usr-id-00001 | profile_key-00001 | profile_value-00001   |
    | usr-id-00002 | profile_key-00002 |    profile_value-00002      |
    | usr-id-00003 | profile_key-00003 | profile_value-00003   |
    | usr-id-00004 | profile_key-00004 |    profile_value-00004      |
    | usr-id-00005 | profile_key-00005 | profile_value-00005   |
    +--------------+-------------------+-----------------------+
    5 rows in set (0.00 sec)
    Then you use REPLACE to remove the tabulations

    Code:
    UPDATE test_table
    SET profile_value = REPLACE(profile_value, CHAR(9), '')
    WHERE profile_value REGEXP '^\t.+\t$';
    
    SELECT * FROM test_table;
    And you can check to see whether the result corresponds to what you were looking for

    Code:
    Query OK, 2 rows affected (0.06 sec)
    Rows matched: 5  Changed: 2  Warnings: 0
    
    +--------------+-------------------+---------------------+
    | user_id      | profile_key       | profile_value       |
    +--------------+-------------------+---------------------+
    | usr-id-00001 | profile_key-00001 | profile_value-00001 |
    | usr-id-00002 | profile_key-00002 | profile_value-00002 |
    | usr-id-00003 | profile_key-00003 | profile_value-00003 |
    | usr-id-00004 | profile_key-00004 | profile_value-00004 |
    | usr-id-00005 | profile_key-00005 | profile_value-00005 |
    +--------------+-------------------+---------------------+
    5 rows in set (0.00 sec)
    
    mysql>

    I use MySQL Community Server 5.5.30 under Linux (Fedora Core 17 X86_64) and the above works for me.


    Regards,
    Dariyoosh

    Comments on this post

    • ishnid agrees
    Last edited by dariyoosh; March 7th, 2013 at 08:52 AM.
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    17
    Rep Power
    0
    thank you but for me again its showing zero rows affected.i am damn sure that my string contains tab characters.
  16. #9
  17. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Location
    Iran
    Posts
    149
    Rep Power
    140
    Originally Posted by sajanmurugan
    thank you but for me again its showing zero rows affected.i am damn sure that my string contains tab characters.
    What you have done in terms of test in order to prove that there are tabulations?
    Have you tried something similar to this?

    Code:
    mysql> SELECT INSTR('value', '\t') FROM DUAL;
    +----------------------+
    | INSTR('value', '\t') |
    +----------------------+
    |                    0 |
    +----------------------+
    1 row in set (0.00 sec)
    
    mysql> 
    mysql> 
    mysql> SELECT INSTR('value\t', '\t') FROM DUAL;
    +------------------------+
    | INSTR('value\t', '\t') |
    +------------------------+
    |                      6 |
    +------------------------+
    1 row in set (0.00 sec)
    
    mysql> 
    mysql>
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    17
    Rep Power
    0
    i can see it in database. and i tried inserting \t to one string then i executed your query then also i got same result.
  20. #11
  21. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Location
    Iran
    Posts
    149
    Rep Power
    140
    Originally Posted by sajanmurugan
    i can see it in database. and i tried inserting \t to one string then i executed your query then also i got same result.
    Does the following returns any output for your table?

    Code:
    SELECT * FROM change_user_profiles
    WHERE profile_value REGEXP '^\t.+\t$';
    Human eye is not a reference here, if the above query doesn't return anything, this means that no profile_value starts and terminates with \t.
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    17
    Rep Power
    0
    ok ok.i got it. i am facing 2 more problems in database.
    in my application there r 4 dropdowns namely state,city,location and bloodgroup.
    based on the state cities sholud come in dropdown and based on city location should come.
    there r 35 states in database and 20 states working properly(means based on the state cities and locations r coming).but the real problem is is with other 15 states.
    city dropdown coontais following
    --select--
    All
    cities names


    similarly location dropdown
    --select--
    All
    location names

    1)for this 15 states when i select state only --select-- and All
    is coming in city and location dropdown.but in database cities and locations are present.

    2)for some states both cities and locations are coming but
    after 'All' there is a big space in the dropdown
    like this --select--
    All


    cities names
    similarly for locations also.

    pls help me to solve above issues.
  24. #13
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by sajanmurugan
    pls help me to solve above issues.
    these are not database issues

    please identify which application language you're using (e.g. php) and i'll move your thread to that forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    17
    Rep Power
    0
    yes its php only.

IMN logo majestic logo threadwatch logo seochat tools logo