In message <[EMAIL PROTECTED]>, Mark Worsdall
<[EMAIL PROTECTED]> writes
>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.
>
So after being advised to do it with regexp I am still stuck:-
I got the 1st case working:-
SELECT Sequence, Message, id FROM lll.cat1stDictionary WHERE Sequence
REGEXP("^66;")
But the 2nd sequence search is a problem:-
SELECT Sequence, Message, id FROM lll.cat1stDictionary WHERE Sequence
REGEXP("^.;66$") OR Sequence REGEXP("^.;66;$")
SELECT Sequence, Message, id FROM lll.cat1stDictionary WHERE Sequence
REGEXP("^.;66$")
and have no idea how to do a 3rd one or nth one.
Anyone help?
--
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
