Query with substring
Hi!
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.
Bye
Marco
Subject
Views
Written By
Posted
Query with substring
57572
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.