Re: How to search based on first LETTER of consecutive words

[Rick James]

These are close:
WHERE field_name LIKE '%t% i% n%'
or
WHERE field_name REGEXP '[[:<:]][Tt].*[[:<:]][Ii].*[[:<:]][Nn]'
But they would match
"This is a new car"

REGEXP '[[:<:]][Tt][[:alpha:]]* [Ii][[:alpha:]]* [Nn]';
would come closer to what you want. However, it requires one space between words -- no punctuation, etc.

mysql> SELECT 'This is new car' LIKE '%t% i% n%';
+------------------------------------+
| 'This is new car' LIKE '%t% i% n%' |
+------------------------------------+
|                                  1 |
+------------------------------------+
mysql> SELECT 'This is a new car' LIKE '%t% i% n%';
+--------------------------------------+
| 'This is a new car' LIKE '%t% i% n%' |
+--------------------------------------+
|                                    1 |
+--------------------------------------+
mysql> SELECT 'This is new car' REGEXP '[[:<:]][Tt].*[[:<:]][Ii].*[[:<:]][Nn]';
+------------------------------------------------------------------+
| 'This is new car' REGEXP '[[:<:]][Tt].*[[:<:]][Ii].*[[:<:]][Nn]' |
+------------------------------------------------------------------+
|                                                                1 |
+------------------------------------------------------------------+
mysql> SELECT 'This is a new car' REGEXP '[[:<:]][Tt].*[[:<:]][Ii].*[[:<:]][Nn]';
+--------------------------------------------------------------------+
| 'This is a new car' REGEXP '[[:<:]][Tt].*[[:<:]][Ii].*[[:<:]][Nn]' |
+--------------------------------------------------------------------+
|                                                                  1 |
+--------------------------------------------------------------------+

mysql> SELECT 'This is new car' REGEXP '[[:<:]][Tt][[:alpha:]]* [Ii][[:alpha:]]* [Nn]';
+--------------------------------------------------------------------------+
| 'This is new car' REGEXP '[[:<:]][Tt][[:alpha:]]* [Ii][[:alpha:]]* [Nn]' |
+--------------------------------------------------------------------------+
|                                                                        1 |
+--------------------------------------------------------------------------+
mysql> SELECT 'This is a new car' REGEXP '[[:<:]][Tt][[:alpha:]]* [Ii][[:alpha:]]* [Nn]';
+----------------------------------------------------------------------------+
| 'This is a new car' REGEXP '[[:<:]][Tt][[:alpha:]]* [Ii][[:alpha:]]* [Nn]' |
+----------------------------------------------------------------------------+
|                                                                          0 |
+----------------------------------------------------------------------------+
1 row in set (0.01 sec)

(Note: 0 means FALSE, 1 means TRUE.)