Professional Web Applications Themes

Reverse like query? - MySQL

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

  1. #1

    Default 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


    Rune Guest

  2. #2

    Default 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-----
    Stefan Rybacki Guest

  3. #3

    Default Re: Reverse like query?

    "Stefan Rybacki" <stefan.rybackigmx.net> skrev i en meddelelse
    news:43uig6F1pje2gU1individual.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 Guest

  4. #4

    Default Re: Reverse like query?

    "Rune" <slamslam.slam> skrev i en meddelelse
    news:43da150b$0$15788$14726298news.sunsite.dk...
    > "Stefan Rybacki" <stefan.rybackigmx.net> skrev i en meddelelse
    > news:43uig6F1pje2gU1individual.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 Guest

  5. #5

    Default Re: Reverse like query?

    "Rune" <slamslam.slam> skrev i en meddelelse
    news:43da1619$0$15785$14726298news.sunsite.dk...
    > "Rune" <slamslam.slam> skrev i en meddelelse
    > news:43da150b$0$15788$14726298news.sunsite.dk...
    >> "Stefan Rybacki" <stefan.rybackigmx.net> skrev i en meddelelse
    >> news:43uig6F1pje2gU1individual.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 Guest

  6. #6

    Default 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';

    [url]http://dev.mysql.com/doc/refman/5.1/en/string-comparison-functions.html[/url]

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  7. #7

    Default Re: Reverse like query?

    "Jerry Stuckle" <jstucklexattglobal.net> skrev i en meddelelse
    news:1_-dnfVB6qcXgUfeRVn-owcomcast.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';
    >
    > [url]http://dev.mysql.com/doc/refman/5.1/en/string-comparison-functions.html[/url]
    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


    Rune Guest

  8. #8

    Default Re: Reverse like query?

    In article <43da1c84$0$15788$14726298news.sunsite.dk>,
    "Rune" <slamslam.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 Guest

  9. #9

    Default Re: Reverse like query?

    "Harald Fuchs" <hf0923xprotecting.net> skrev i en meddelelse
    news:87d5idrdq5.fsfsrv.protecting.net...
    > In article <43da1c84$0$15788$14726298news.sunsite.dk>,
    > "Rune" <slamslam.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.


    Rune Guest

Similar Threads

  1. Replies: 10
    Last Post: November 2nd, 10:32 AM
  2. How to reverse a list?
    By jrefactors@hotmail.com in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: October 24th, 02:51 AM
  3. Motions in reverse
    By meathelix webforumsuser@macromedia.com in forum Macromedia Director 3D
    Replies: 1
    Last Post: August 29th, 05:21 AM
  4. Can a .swf be reverse engineered?
    By EDunn webforumsuser@macromedia.com in forum Macromedia Flash Sitedesign
    Replies: 3
    Last Post: July 11th, 06:03 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139