Wild card character issue

[JadedLucidity]

mysql version 5.0.24

I hoping someone here can help me with this. I have 3 queries:

Works ->SELECT words FROM vocabulary WHERE words LIKE '%ate%';

Works ->SELECT words FROM vocabulary WHERE words LIKE 'ate%';

Does not work ->SELECT words FROM vocabulary WHERE words LIKE '%ate’;

I was wondering if anyone knows why the wildcard character doesn't work with % in the front to search the end of the string? Is there another way to search the end of a field?

Thanks

[effigy]

Is the funny quote at the end of your post also in your code? '%ate[b]’[/b]

[JadedLucidity]

ok  :)
SELECT words FROM vocabulary where words like '%ate'; doesn't work eigther. I don't know how the quote got changed to a curly one. lol All I am doing with these queries is changing the location of the % and the one with the % in the front only doesn't seem to want to work. I know there are things in the list ending in %ate. debate, communicate, etc.

[effigy]

This should work. Are you sure your data is clean? Are you getting any errors from the query? Have you tried the query from the command line?

[JadedLucidity]

O good grief I'm an idiot.  :-\ There is a pilcrow character at the end of each of the words in the word list. I imported all the words out of a text file and it appended each with a ¶ to the end of each. There are well over 50,000 words in this db, any ideas how I can get this off without having to manually edit each one? ???

[effigy]

Use a select statement to make sure this works properly before using update. Here is how I tested this:

[code]
mysql> insert into tmp (txt) values ('one');
Query OK, 1 row affected (0.03 sec)

mysql> insert into tmp (txt) values ('two');
Query OK, 1 row affected (0.04 sec)

mysql> insert into tmp (txt) values ('three');
Query OK, 1 row affected (0.03 sec)

mysql> select * from tmp;
+----+-------+
| id | txt  |
+----+-------+
| 1  | one  |
| 2  | two  |
| 3  | three |
+----+-------+
3 rows in set (0.01 sec)

mysql> update tmp set txt = concat(txt, x'c2b6');
Query OK, 3 rows affected (0.03 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from tmp;
+----+---------+
| id | txt    |
+----+---------+
| 1  | one¶  |
| 2  | two¶  |
| 3  | three¶ |
+----+---------+
3 rows in set (0.00 sec)

mysql> update tmp set txt = replace(txt, x'c2b6', '');
Query OK, 3 rows affected (0.03 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from tmp;
+----+-------+
| id | txt  |
+----+-------+
| 1  | one  |
| 2  | two  |
| 3  | three |
+----+-------+
3 rows in set (0.00 sec)

mysql> exit;
[/code]

[JadedLucidity]

ok close apparently there is another something there before the pilcrow b6. I've attached an image of what it looks like if I try to go in and edit. It's like there are 2 lines of text or a hard return? When I go in to edit it automatically tried to go to the line below the word.

[attachment deleted by admin]

[effigy]

What are you editing this in? I assume your editor is showing the pilcrow to represent a new line.

To find out what's really in your field, if you're on Unix, go to MySQL's bin at the command line and run:

[code]./mysql -p database_name -BNe 'select * from table_name limit 1' | od -cx[/code]

[JadedLucidity]

Alright,

Thank you for your help  :-*. Apparently I loaded it wrong. I should have used:

LOAD DATA INFILE 'topdown.txt' INTO TABLE vocab
  LINES TERMINATED BY '\r\n';

Where I just used the :
LOAD DATA INFILE 'topdown.txt' INTO TABLE vocab;

without using the lines terminated by command. Could have saved myself some issues. Now my data doesn't have any pilcrows, new lines, spaces, or whatnot.

Thank you very much  :)