Like predicate to match a whole word only

likeselectsqlite

I have a SQLite database with a table named minecraft.

+----+----------------------+
| id |         name         |
+----+----------------------+
|  1 | Pocket Mine MP       |
|  2 | Open Computers       |
|  3 | hubot minecraft skin |
|  4 | Terasology           |
|  5 | msm                  |
+----+----------------------+

I need to find all the records which have 'e' and 'o' in their 'name' field. Here is my Select query:

select * from minecraft where name like '%e%o%'

Here is the result of the above query:

+----+----------------+
| id |      name      |
+----+----------------+
|  2 | Open Computers |
|  4 | Terasology     |
+----+----------------+

The problem is that the Like predicate matches the entire value, not words. The row with id = 2 should not be matched, because all the criteria didn't happen in a single word ('e' is found in the first word and 'o' in the other word): Open Computers.

How should I change my Select query so it matches the only row with 'e' and 'o' in a single word?

+----+------------+
| id |    name    |
+----+------------+
|  4 | Terasology |
+----+------------+

Thanks

Best Answer

Your best bet is probably to use REGEXP, which needs some special installation prior to being usable.

In that case, you'd use:

WHERE         
    name REGEXP 'e\w*o'

This regular expression means: (a literal 'e') followed by (zero or more word characters) followed by (a literal 'o').

This approach won't shy away from cases like 'This hello will work'

References:

Alternative:

If you can't install the required packages to have REGEXP available, you can probably use a user-defined function to perform the "word-by-word" LIKE check. Let's imagine it's called check_words_like(sentence, like_predicate), I'd use in the following fashion:

SELECT
    whatever
FROM
    (SELECT
        name, whatever
    FROM
        tbl
    WHERE
        name like '%e%o%'
    ) AS q1
WHERE
    check_words_like(q1.name, '%e%o%') ;

This still requires some C programming and interfacing, and might be as complicated as getting the right REGEXP package.

Related Solutions

Why doesn’t INSERT AFTER Trigger add all results

On most platforms your insert would throw an error if your subselect returns more than one row, rather than just insert the 'first' returned row (and 'first' is undefined without an order by clause).

What you are probably after is an insert like this:

INSERT Into Likes 
SELECT New.ID, ID 
FROM Highschooler 
WHERE Grade = New.Grade and ID<>New.ID;

Sql-server – Can someone explain why select with nolock will query a potion of updated data

It is not quite true that NOLOCK means placing no locks at all. Queries under this hint will still take Sch-S locks and (possibly HOBT locks).

Under read committed isolation level SQL Server will (usually) take row level S locks and release them as soon as the data is read. These are incompatible with the X locks held on uncommited updates and thus prevent dirty reads.

In the example in the linked answer the SELECT query is not blocked when it encounters a modified row so reading partial updates is quite likely.

It can also happen at default read committed isolation level too though that a SELECT reads some rows with the "before" value and others with the "after" value. It is just needed to engineer a situation where

Select query reads value of row R1 and releases its S lock
Update query updates R2 and takes an X lock
Select query tries to read R2 and is blocked.
Update query updates R1 and takes an X lock.
Update transaction commits thus releasing its locks and allowing the Select to read R2

This type of situation might arise for example if the SELECT and UPDATE are using different indexes to locate the rows of interest.

Example

CREATE TABLE T
(
X INT IDENTITY PRIMARY KEY,
Y AS -X UNIQUE,
Name varchar(10),
Filler char(4000) DEFAULT 'X'
)


INSERT INTO T (Name)
SELECT TOP 2500 'A'
FROM master..spt_values

Now in one query window run

DECLARE @Sum int

SELECT 'SET @@ROWCOUNT' WHERE 1=0

WHILE (@@ROWCOUNT = 0)
SELECT @Sum = SUM(LEN(Name))
FROM T 
WHERE Y IN (-1, -2500)
HAVING SUM(LEN(Name)) = 3

This will run in an infinite loop. In another run

UPDATE T 
SET Name=CASE WHEN Name = 'A' THEN 'AA' ELSE 'A' END

This will likely stop the loop in the other query (try again if not) meaning that it must have read either A,AA or AA,A

Best Answer

Related Solutions

Why doesn’t INSERT AFTER Trigger add all results

Sql-server – Can someone explain why select with nolock will query a potion of updated data

Related Question