Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2011
    Posts
    4

    Unanswered: Query to return records that are comprised exclusively of a certain set of letters

    I have text field, [Word], of single words, and I am looking for records that contain a set of letters, and only those letters.

    e.g. a statement that queried [Word] for records containing any of the set, but only the set, {isltne} would return the records "Listen", "Silent", "Isle", "Tile", "Set", etc.

    How do I write this query?

  2. #2
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    Does the word "little" count? It has only got letters from the set, but some of the letters are used twice.

    The query you are asking for will be pretty big. I suppose it will contain the SQL functions SUBSTRING, CHAR_LENGHTH, and perhaps also POSITION. (Depending on if letters are allowed to be re-used or not.)

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    It's quite easy if you have a DBMS that supports regular expressions (I don't think there is an ANSI syntax for them).

    In PostgreSQL this would be something like this (provided the word "Little" is valid]
    Code:
    SELECT *
    FROM your_table
    WHERE your_column ~* '[isltne]';

  4. #4
    Join Date
    Nov 2011
    Posts
    4
    Yes, "Little" is a valid return.

    I'm using iList Data, which doesn't support a query with ~ in it

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by clarebear View Post
    I'm using iList Data, which doesn't support a query with ~ in it
    Then check the manual if it supports regular expressions in some other way.

  6. #6
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    Or something as simple as:

    select * from wordstable
    where substring(col from 1 for 1) in ('i','s','l','t','n','e',' ')
    and substring(col from 2 for 1) in ('i','s','l','t','n','e',' ')
    and substring(col from 3 for 1) in ('i','s','l','t','n','e',' ')
    and substring(col from 4 for 1) in ('i','s','l','t','n','e',' ')
    and substring(col from 5 for 1) in ('i','s','l','t','n','e',' ')
    and substring(col from 6 for 1) in ('i','s','l','t','n','e',' ')
    ...;


    Ansi Core SQL-99

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it's gotta be a bit more complicated than that, Jarl

    for instance, that code wouldn't find the word 'isle'

    also, the space is wrong, as i don't believe any single word contains a space
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2011
    Posts
    4
    @Shammat, what is a "regular expression"? Please pardon the noob-ness of my question

    @r937 and @JarlH - r937 is right, this must return single words, so no spaces.

    So let me give you more detail.

    Let's just say, for example, that I wanted to use this program for Scrabble - hypothetically speaking. So in this example, my table would be every single word in the ENABLE list (a .txt file of all acceptable words in Scrabble), and I would have 7 letters in front of me, and therefore need to pull entries that are 3-7 characters in length that contain the set (or any subset) of my 7 letters, and only those letters.

    Ok fine, I admit it! This is for words with friends. I'm an addict. But its so time consuming! I figure with a database, it'll go much quicker. And it won't really feel like cheating - because I'm (with your help!!) putting so much work and ingenuity behind it

  9. #9
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    The reason I added a space character was to take care of trailing blanks in case of padding. If each row contains just one word, the space wont do any harm. (If varchar data type, the space character is unnecessary, but still harmless.)

  10. #10
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by clarebear View Post
    @Shammat, what is a "regular expression"? Please pardon the noob-ness of my question
    Check out this site:
    Regular-Expressions.info - Regex Tutorial, Examples and Reference - Regexp Patterns

    They are pretty confusing when you first use them (at least for me...) but once you get the hang of them, a lot of things get a lot easier...

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    TRANSLATE function may work.

    DB2 syntax:
    Code:
    >>-TRANSLATE--(--char-string-exp-------------------------------->
    
    >--+-----------------------------------------------------------+-->
       |                                       .-,--' '----------. |   
       '-,--to-string-exp--,--from-string-exp--+-----------------+-'   
                                               '-,--pad-char-exp-'     
    
    >--)-----------------------------------------------------------><
    Corresponding ANSI syntax may be:
    Code:
    <character transliteration> ::=
        TRANSLATE <left paren> <character value expression>
        USING <transliteration name> <right paren>

    Note: Examples were tested on DB2 9.7.5 on Windows.

    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     words(word) AS (
    VALUES
      'Listen' , 'Silent'  , 'Isle'  , 'Tile' , 'Set' , 'Little'
    , 'is tne' , 'Silence' , 'aisle'
    )
    SELECT word
     FROM  words
     WHERE TRANSLATE(LOWER(word) , '*' , ' isltne') = ''
    ;
    ------------------------------------------------------------------------------
    
    WORD   
    -------
    Listen 
    Silent 
    Isle   
    Tile   
    Set    
    Little 
    
      6 record(s) selected.
    Example 2:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     words(word) AS (
    VALUES
      'Listen' , 'Silent'  , 'Isle'  , 'Tile' , 'Set' , 'Little'
    , 'is tne' , 'Silence' , 'aisle'
    )
    SELECT word
         , CASE
           WHEN TRANSLATE(LOWER(word) , '*' , ' isltne') = '' THEN
                'Yes'
           ELSE 'No'
           END  AS "{isltne} only?"
     FROM  words
    ;
    ------------------------------------------------------------------------------
    
    WORD    {isltne} only?
    ------- --------------
    Listen  Yes           
    Silent  Yes           
    Isle    Yes           
    Tile    Yes           
    Set     Yes           
    Little  Yes           
    is tne  No            
    Silence No            
    aisle   No            
    
      9 record(s) selected.
    Last edited by tonkuma; 02-06-12 at 19:35.

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Tested on Mimer SQL Developers - Mimer SQL-2003 Validator

    Both of Example 1 and Example 2 were...
    Code:
    Result:
    
    The following features outside Core SQL-2003 are used:
    
    F641, "Row and table constructors"
    T121, "WITH (excluding RECURSIVE) in query expression"
    F661, "Simple tables"

Posting Permissions

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