php dropdowns (was "removing tab characters")

Dev Shed Forums Sponsor:

March 7th, 2013, 04:33 AM

sajanmurugan

Registered User

Join Date: Mar 2013

Posts: 17

Time spent in forums: 2 h 25 m 3 sec
Reputation Power: 0

Tab characters

March 7th, 2013, 05:07 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,375

Time spent in forums: 3 Months 1 Week 2 Days 7 h 30 m 16 sec
Reputation Power: 4140

Code:

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

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

March 7th, 2013, 05:23 AM

sajanmurugan

Registered User

Join Date: Mar 2013

Posts: 17

Time spent in forums: 2 h 25 m 3 sec
Reputation Power: 0

will it affect on other data???? becuase that table contains very imp data...
thank u

March 7th, 2013, 05:30 AM

sajanmurugan

Registered User

Join Date: Mar 2013

Posts: 17

Time spent in forums: 2 h 25 m 3 sec
Reputation 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

March 7th, 2013, 05:41 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,375

Time spent in forums: 3 Months 1 Week 2 Days 7 h 30 m 16 sec
Reputation Power: 4140

Quote:

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

March 7th, 2013, 07:22 AM

sajanmurugan

Registered User

Join Date: Mar 2013

Posts: 17

Time spent in forums: 2 h 25 m 3 sec
Reputation 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 :-(

March 7th, 2013, 07:48 AM

dariyoosh

Contributing User

Join Date: Nov 2012

Location: Iran / France

Posts: 132

Time spent in forums: 2 Days 6 h 25 m 17 sec
Reputation Power: 133

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 07:52 AM.

March 8th, 2013, 01:05 AM

sajanmurugan

Registered User

Join Date: Mar 2013

Posts: 17

Time spent in forums: 2 h 25 m 3 sec
Reputation Power: 0

thank you but for me again its showing zero rows affected.i am damn sure that my string contains tab characters.

March 8th, 2013, 03:16 AM

dariyoosh

Contributing User

Join Date: Nov 2012

Location: Iran / France

Posts: 132

Time spent in forums: 2 Days 6 h 25 m 17 sec
Reputation Power: 133

Quote:

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>

#10

March 8th, 2013, 04:29 AM

sajanmurugan

Registered User

Join Date: Mar 2013

Posts: 17

Time spent in forums: 2 h 25 m 3 sec
Reputation 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.

#11

March 8th, 2013, 04:49 AM

dariyoosh

Contributing User

Join Date: Nov 2012

Location: Iran / France

Posts: 132

Time spent in forums: 2 Days 6 h 25 m 17 sec
Reputation Power: 133

Quote:

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.

#12

March 8th, 2013, 08:19 AM

sajanmurugan

Registered User

Join Date: Mar 2013

Posts: 17

Time spent in forums: 2 h 25 m 3 sec
Reputation 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.