Reverse like query?
This is a discussion on Reverse like query? within the MySQL forums, part of the Database Server Software category; --> I have some problems figuring this one out. How do I put together a query that selects those rows ...
| |||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-28-2008, 07:18 AM
| ||||
| ||||
 Reverse like query? I have some problems figuring this one out. How do I put together a query that selects those rows in which a field contains a substring of another given string. e.g. table test (test varchar(255)) row1: this is a test row2: test row3: a test row4: that If I have a string called "a test", then I want to select row 1 + 3, but not 2 and 4. thanks /Rune      |
|
02-28-2008, 07:18 AM
| |||
| |||
| Re: Reverse like query? -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Rune schrieb: > I have some problems figuring this one out. How do I put together a query > that selects those rows in which a field contains a substring of another > given string. > > e.g. > > table test (test varchar(255)) > > row1: this is a test > row2: test > row3: a test > row4: that > > If I have a string called "a test", then I want to select row 1 + 3, but not > 2 and 4. > > thanks > /Rune > > SELECT * FROM table WHERE test LIKE '%a test%' Regards Stefan -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.2 (MingW32) iD8DBQFD2hQGyeCLzp/JKjARAgcKAJ9bnreVPkAexS77/7/HhmgKQc0oqgCdGvCS Gy/hlNFk38SepsZgCSxPtaU= =V8jz -----END PGP SIGNATURE----- |
|
02-28-2008, 07:18 AM
| |||
| |||
| Re: Reverse like query? "Stefan Rybacki" <stefan.rybacki@gmx.net> skrev i en meddelelse news:43uig6F1pje2gU1@individual.net... > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Rune schrieb: >> I have some problems figuring this one out. How do I put together a query >> that selects those rows in which a field contains a substring of another >> given string. >> >> e.g. >> >> table test (test varchar(255)) >> >> row1: this is a test >> row2: test >> row3: a test >> row4: that >> >> If I have a string called "a test", then I want to select row 1 + 3, but >> not >> 2 and 4. >> >> thanks >> /Rune >> >> > > SELECT * FROM table WHERE test LIKE '%a test%' > > Regards > Stefan Dang! I put the question wrong. I keep mixing it up, because it's the oppositite of what I usally would need. I need to return all those rows where all the field is in the given string. It should return rows: 2+3, but not 1,4 1) "this is a test" is not part of "a test" : FAULT 2) "test" is part of "a test" : OK 3) "a test" is part of "a test" : OK 4) "that" is not part of "a test": FAULT |
|
02-28-2008, 07:18 AM
| |||
| |||
| Re: Reverse like query? "Rune" <slam@slam.slam> skrev i en meddelelse news:43da150b$0$15788$14726298@news.sunsite.dk... > "Stefan Rybacki" <stefan.rybacki@gmx.net> skrev i en meddelelse > news:43uig6F1pje2gU1@individual.net... >> -----BEGIN PGP SIGNED MESSAGE----- >> Hash: SHA1 >> >> Rune schrieb: >>> I have some problems figuring this one out. How do I put together a >>> query >>> that selects those rows in which a field contains a substring of another >>> given string. >>> >>> e.g. >>> >>> table test (test varchar(255)) >>> >>> row1: this is a test >>> row2: test >>> row3: a test >>> row4: that >>> >>> If I have a string called "a test", then I want to select row 1 + 3, but >>> not >>> 2 and 4. >>> >>> thanks >>> /Rune >>> >>> >> >> SELECT * FROM table WHERE test LIKE '%a test%' >> >> Regards >> Stefan > > Dang! I put the question wrong. I keep mixing it up, because it's the > oppositite of what I usally would need. I need to return all those rows > where all the field is in the given string. It should return rows: 2+3, > but not 1,4 > > 1) "this is a test" is not part of "a test" : FAULT > 2) "test" is part of "a test" : OK > 3) "a test" is part of "a test" : OK > 4) "that" is not part of "a test": FAULT Something like this: SELECT * from test WHERE "a test" like %test%; But this won't work. |
|
02-28-2008, 07:18 AM
| |||
| |||
| Re: Reverse like query? "Rune" <slam@slam.slam> skrev i en meddelelse news:43da1619$0$15785$14726298@news.sunsite.dk... > "Rune" <slam@slam.slam> skrev i en meddelelse > news:43da150b$0$15788$14726298@news.sunsite.dk... >> "Stefan Rybacki" <stefan.rybacki@gmx.net> skrev i en meddelelse >> news:43uig6F1pje2gU1@individual.net... >>> -----BEGIN PGP SIGNED MESSAGE----- >>> Hash: SHA1 >>> >>> Rune schrieb: >>>> I have some problems figuring this one out. How do I put together a >>>> query >>>> that selects those rows in which a field contains a substring of >>>> another >>>> given string. >>>> >>>> e.g. >>>> >>>> table test (test varchar(255)) >>>> >>>> row1: this is a test >>>> row2: test >>>> row3: a test >>>> row4: that >>>> >>>> If I have a string called "a test", then I want to select row 1 + 3, >>>> but not >>>> 2 and 4. >>>> >>>> thanks >>>> /Rune >>>> >>>> >>> >>> SELECT * FROM table WHERE test LIKE '%a test%' >>> >>> Regards >>> Stefan >> >> Dang! I put the question wrong. I keep mixing it up, because it's the >> oppositite of what I usally would need. I need to return all those rows >> where all the field is in the given string. It should return rows: 2+3, >> but not 1,4 >> >> 1) "this is a test" is not part of "a test" : FAULT >> 2) "test" is part of "a test" : OK >> 3) "a test" is part of "a test" : OK >> 4) "that" is not part of "a test": FAULT > > Something like this: SELECT * from test WHERE "a test" like %test%; > But this won't work. select * from test where test in ("a test") : returns row 3) but not 2) select * from test where %test% in ("a test"): is syntax error Alas! |
|
02-28-2008, 07:18 AM
| |||
| |||
| Re: Reverse like query? Rune wrote: > I have some problems figuring this one out. How do I put together a query > that selects those rows in which a field contains a substring of another > given string. > > e.g. > > table test (test varchar(255)) > > row1: this is a test > row2: test > row3: a test > row4: that > > If I have a string called "a test", then I want to select row 1 + 3, but not > 2 and 4. > > thanks > /Rune > > SELECT * FROM table WHERE data LIKE '%a test'; http://dev.mysql.com/doc/refman/5.1/...functions.html -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstucklex@attglobal.net ================== |
|
02-28-2008, 07:18 AM
| |||
| |||
| Re: Reverse like query? "Jerry Stuckle" <jstucklex@attglobal.net> skrev i en meddelelse news:1_-dnfVB6qcXgUfeRVn-ow@comcast.com... > Rune wrote: >> I have some problems figuring this one out. How do I put together a query >> that selects those rows in which a field contains a substring of another >> given string. >> >> e.g. >> >> table test (test varchar(255)) >> >> row1: this is a test >> row2: test >> row3: a test >> row4: that >> >> If I have a string called "a test", then I want to select row 1 + 3, but >> not 2 and 4. >> >> thanks >> /Rune > > > SELECT * FROM table WHERE data LIKE '%a test'; > > http://dev.mysql.com/doc/refman/5.1/...functions.html Nope. I'm sorry. I put the question wrong. I keep mixing it up, because it's the oppositite of what I usally would need. I need to return all those rows where all of the field is in the given string. It should return rows: 2+3, but not 1,4 1) "this is a test" is not part of "a test" : FAULT 2) "test" is part of "a test" : OK 3) "a test" is part of "a test" : OK 4) "that" is not part of "a test": FAULT Something like SELECT * from test WHERE "a test" like %test%; - but that doesn't work on MySQL |
|
02-28-2008, 07:18 AM
| |||
| |||
| Re: Reverse like query? In article <43da1c84$0$15788$14726298@news.sunsite.dk>, "Rune" <slam@slam.slam> writes: >>> Dang! I put the question wrong. I keep mixing it up, because it's the >>> oppositite of what I usally would need. I need to return all those rows >>> where all the field is in the given string. It should return rows: 2+3, >>> but not 1,4 >>> >>> 1) "this is a test" is not part of "a test" : FAULT >>> 2) "test" is part of "a test" : OK >>> 3) "a test" is part of "a test" : OK >>> 4) "that" is not part of "a test": FAULT >> >> Something like this: SELECT * from test WHERE "a test" like %test%; >> But this won't work. > select * from test where test in ("a test") : returns row 3) but not 2) > select * from test where %test% in ("a test"): is syntax error Use the following: SELECT whatever FROM test WHERE 'a test' LIKE concat('%', test, '%') |
|
02-28-2008, 07:18 AM
| ||||
| ||||
| Re: Reverse like query? "Harald Fuchs" <hf0923x@protecting.net> skrev i en meddelelse news:87d5idrdq5.fsf@srv.protecting.net... > In article <43da1c84$0$15788$14726298@news.sunsite.dk>, > "Rune" <slam@slam.slam> writes: > >>>> Dang! I put the question wrong. I keep mixing it up, because it's the >>>> oppositite of what I usally would need. I need to return all those rows >>>> where all the field is in the given string. It should return rows: 2+3, >>>> but not 1,4 >>>> >>>> 1) "this is a test" is not part of "a test" : FAULT >>>> 2) "test" is part of "a test" : OK >>>> 3) "a test" is part of "a test" : OK >>>> 4) "that" is not part of "a test": FAULT >>> >>> Something like this: SELECT * from test WHERE "a test" like %test%; >>> But this won't work. > >> select * from test where test in ("a test") : returns row 3) but not 2) >> select * from test where %test% in ("a test"): is syntax error > > Use the following: > > SELECT whatever > FROM test > WHERE 'a test' LIKE concat('%', test, '%') Yes! Just what I need. Thanks. |
Linear Mode
