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

-----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-----

"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

"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.

"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!

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
==================

"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

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, '%')

"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.