MySQL Forums
Forum List  »  IBM DB2

Query with substring
Posted by: Marco Bonfatti
Date: July 12, 2006 02:09AM

I've migrated a db (and query) from a DB2 server to a MySql server.

I had a query:
"SELECT * FROM item, author WHERE item.i_a_id = author.a_id AND item.i_title LIKE 'my_search_string' ORDER BY item.i_title FETCH FIRST 50 ROWS ONLY"

A person suggested me to convert it with:
"SELECT * FROM ITEM, AUTHOR WHERE ITEM.I_A_ID = AUTHOR.A_ID AND substring(soundex(ITEM.I_TITLE), 0, 4)=substring(soundex('my_search_string'), 0, 4) ORDER BY ITEM.I_TITLE LIMIT 50"

I know that the function "substring" accepts the pos parameter > 0... but that's the problem!!!

If I put in the WHERE condition
"substring(soundex(ITEM.I_TITLE), 1, 4) = substring(soundex('my_search_string'), 1, 4)"
there's no results; instead, if i put
"substring(soundex(ITEM.I_TITLE), 0, 4) = substring(soundex('my_search_string'), 0, 4)"
the query spends a lot of time (about 5 seconds) but returns something. What's going on? I'm stuck.


Options: ReplyQuote

Written By
Query with substring
July 12, 2006 02:09AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.