Re: How to search based on first LETTER of consecutive words
Posted by:
Rick James
Date: April 04, 2013 10:34AM
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.)
Subject
Views
Written By
Posted
6001
April 02, 2013 03:28PM
Re: How to search based on first LETTER of consecutive words
2644
April 04, 2013 10:34AM
2436
April 28, 2013 11:42AM
2341
April 28, 2013 09:16PM
2448
April 29, 2013 02:02PM
2211
April 30, 2013 08:10PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.