Hi all,
I have a problem, I have some VARCHAR data in a column called Sequence
looking like this:-
5;6
12;6
120;6
5;6;1
12;6;12
120;6;105
So there could be anything from 2 to n numbers seperated by ;
Each number could be 1 to 3 digits long (eg 5 12 or 121).
I have tried various searches like the following but none of them seem
fool proof, i.e. they miss data:-
# This is for the maximum of 3 sequences searching by the 1st icon
SELECT Sequence, Message, id FROM $sqlTable WHERE Sequence LIKE
'$filterValue;%_%_' ORDER BY Message";
# This is for the maximum of 3 sequences searching by the 2nd icon
SELECT Sequence, Message, id FROM $sqlTable WHERE Sequence LIKE
'_;$filterValue' OR Sequence LIKE '__;$filterValue' OR Sequence LIKE
'%;$filterValue;%' ORDER BY Message";
# This is for the maximum of 3 sequences searching by the 3rd icon
SELECT Sequence, Message, id FROM $sqlTable WHERE Sequence LIKE
'%;%;$filterValue' ORDER BY Message";
'Sequence' is where the numbers to search on are stored.
$sqlTable is a variable containing the name of the table.
$filterValue is a variable containing the search number.
So what rules can I create to work for all examples, though the above
examples are for only 3 sequences of ; seperated numbers, there could be
upto 6 (though I would prefer a rule to cope with n amount).
I am using perl so the OR LIKE part could be created PRE SELECT and then
added onto the SELECT statement.
This one is hurting my head.:-)
M.
--
Work:- postmasterAThinwick.demon.co.uk WEB:- http://www.hinwick.demon.co.uk
Work:- mworsdallATshaftesburysoc.org.uk REPLACE AT with @
Home:- hinwickATworsdall.demon.co.uk WEB:- http://www.wizdom.org.uk
Shadow:- webmasterATshadow.org.uk WEB:- http://www.shadow.org.uk
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
