Regular expression for whether a string can be made from a set of letters

Question

I am going to create a Scrabble helper where you can enter letters and see which words can be made from them. I have a MySQL table containing all the words, but I cannot recover them. Here is what I still have:

SELECT
    word
FROM
    dictionary
WHERE
    word REGEXP '^[example]*$'

but this does not work because it will return words containing more than one a, for example. Is there any way to achieve this?

(I am also open to any methods that do not use regular expressions, although it seems that regular expressions will be the best way to do this).

Answer 1

Here is the solution; there certainly is room for optimization:

DECLARE @Word  VARCHAR(8) = 'Stack'
DECLARE @Avail VARCHAR(8) = 'ACKST'

DECLARE @Letters TABLE
    (
    Letter  CHAR(1) NOT NULL PRIMARY KEY,
    NumNeeded   INT NOT NULL,
    NumAvailable    INT NOT NULL
    )
INSERT INTO @Letters (Letter, NumNeeded, NumAvailable)
    SELECT
        R.Letter, R.NumNeeded, COALESCE(A.NumAvailable, 0) AS NumAvailable
    FROM
            (
            SELECT
                Letter, COUNT(*) AS NumNeeded
            FROM
                (
                SELECT UPPER(SUBSTRING(@Word, N.Number, 1)) AS Letter
                FROM Numbers AS N
                WHERE N.Number BETWEEN 1 AND LEN(@Word)
                ) AS X
            GROUP BY
                Letter
            ) AS R
        LEFT JOIN
            (
            SELECT
                Letter, COUNT(*) AS NumAvailable
            FROM
                (
                SELECT UPPER(SUBSTRING(@Avail, N.Number, 1)) AS Letter
                FROM Numbers AS N
                WHERE N.Number BETWEEN 1 AND LEN(@Word)
                ) AS X
            GROUP BY
                Letter
            ) AS A ON R.Letter = A.Letter

SELECT CASE WHEN EXISTS (SELECT * FROM @Letters WHERE NumNeeded > NumAvailable) THEN 'No' ELSE 'Yes' END AS OK

, Numbers.

Answer 2

.

"".

- ( , ) , , "".