Sql query issue

talat · 10-17-2007, 01:27 PM

Hi Guys

I have a table which has two columns

Code:

mysql> desc email;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| mail | varchar(255) | YES | | NULL | |
| pass | varchar(255) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql>

Now i have a file from which i fill data in this table.The structure of file was like this

emailidpass\n

e.g

talat@xyz.comtalat123\n
shane@xyz.comshane\n

I seperated the email id and password with the following sed command.

Code:

sed 's/xyz.com/xyz.com\t/g' file > newfile

Then i load this file data in my table with following cmd.

Code:

mysql> load data local infile '/home/talat/newfile' into table email;

Code:

mysql> select mail from email where pass= 'anwar';
Empty set (0.06 sec

)

I am unable to understand why this is happening. Plz guide.I have another table in this DB and the the select query is working fine on that table.

Regard
Talat

PTrenholme · 10-17-2007, 02:24 PM

If that select output is correct, it looks like the \n after the password may have been included in the password field. (No idea why that might be true -- I haven't use mysql for several years now.)

Instead of pass= in your query, try a pass like query and see what you get.

talat · 10-18-2007, 06:34 AM

Hi PTrenholme

Its also not working.

Code:

mysql> select * from email where pass like 'anwar';
Empty set (0.06 sec)

Regard
Talat

PTrenholme · 10-18-2007, 08:59 AM

Hum. Try LIKE '%anwar%' If that works, try it without the wild-card at the front or the end. (Some SQLs treat 'LIKE anwar' as if it were a regexp, others as if the 'LIKE' was an '='. I'm not sure about mysql, so I'm suggesting using explicit wild-cards.)

Oh, looking at pinfo mysgl, under function->string->comparisons, I notice that mysql offers a 'REGEXP' and 'RLIKE' comparison operator in addition to a simple 'LIKE'. Perhaps a REGEXP might work better.