Jump to content

Wild card character issue


JadedLucidity

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
Link to comment
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.
Link to comment
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? ???
Link to comment
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]
Link to comment
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]
Link to comment
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]
Link to comment
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  :)
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.