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

clarebear · #1 (**permalink**) 11-13-11, 22:25

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?

JarlH · #2 (**permalink**) 11-14-11, 03:15

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

shammat · #3 (**permalink**) 11-14-11, 03:41

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

clarebear · #4 (**permalink**) 11-14-11, 15:15

Yes, "Little" is a valid return.

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

shammat · #5 (**permalink**) 11-14-11, 15:41

Quote:

Originally Posted by clarebear

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.

JarlH · #6 (**permalink**) 11-15-11, 02:39

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

r937 · #7 (**permalink**) 11-15-11, 04:39

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

clarebear · #8 (**permalink**) 11-15-11, 17:10

@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

JarlH · #9 (**permalink**) 11-16-11, 02:29

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

shammat · #10 (**permalink**) 11-16-11, 02:49

Quote:

Originally Posted by clarebear

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

tonkuma · #11 (**permalink**) 02-06-12, 03:47

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.

tonkuma · #12 (**permalink**) 02-07-12, 08:36

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"

Internet Services

Web Development

Digital Marketing

Consumer Tech