#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2015
    Posts
    3
    Rep Power
    0

    Finding all non-matching strings using LIKE or SIMILAR to


    Given a string (s) of length n, and a CHARACTER(n) column (c) from a table, I want to select all rows where the string in each row does not contain any of the characters from s at the same position.

    For example, let's say s is '1234'. So I want all rows where '1' is not at the first position, '2' is not at the second position and so on. But they can be at any other position.

    So '1567' is not valid, but '2143' is.

    As all strings in c and the string s will always be of the same length n, in this example I could do
    Code:
    ... WHERE c NOT LIKE '1___ AND c NOT LIKE '_2__' AND c NOT like '__3_' AND c NOT LIKE '___4';
    But that gets messier the longer the strings get. So I'm wondering if there isn't a better, cleaner way of achieving this. And I'm hoping someone can tell me the way to do it then. If it helps to know, each character in a string is guaranteed to only occur once in that string.
  2. #2
  3. No Profile Picture
    Super Moderator
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2009
    Location
    Hartford, WI
    Posts
    1,494
    Rep Power
    111
    If this is simply in relation to it's mess on longer strings, would it be viewed the same mess if presented in a list instead of a straight string?
    Code:
    WHERE c NOT LIKE '1___'
    AND c NOT LIKE '_2__'
    AND c NOT like '__3_'
    AND c NOT LIKE '___4'
    He who knows not that he knows not is a fool, ignore him. He who knows that he knows not is ignorant, teach him. He who knows not that he knows is asleep, awaken him. He who knows that he knows is a leader, follow him.

IMN logo majestic logo threadwatch logo seochat tools logo