Jump to content

Archived

This topic is now archived and is closed to further replies.

JadedLucidity

Wild card character issue

Recommended Posts

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

Share this post


Link to post
Share on other sites
Is the funny quote at the end of your post also in your code? '%ate[b]’[/b]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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? ???

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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  :)

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.