Jump to content


Photo

Wild card character issue


  • Please log in to reply
8 replies to this topic

#1 JadedLucidity

JadedLucidity
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 15 August 2006 - 02:08 PM

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


#2 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 15 August 2006 - 02:21 PM

Is the funny quote at the end of your post also in your code? '%ate
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#3 JadedLucidity

JadedLucidity
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 15 August 2006 - 02:28 PM

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.

#4 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 15 August 2006 - 03:15 PM

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?
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#5 JadedLucidity

JadedLucidity
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 15 August 2006 - 03:37 PM

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? ???

#6 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 15 August 2006 - 04:00 PM

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

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;

Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#7 JadedLucidity

JadedLucidity
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 15 August 2006 - 04:49 PM

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]

#8 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 15 August 2006 - 05:11 PM

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:

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

Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#9 JadedLucidity

JadedLucidity
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 15 August 2006 - 05:23 PM

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  :)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users