[SOLVED] Can we use non-greedy pattern matching in MySQL???

[vikram8jp]

MySql server version: 5.0.67

 

MySql code:

 SELECT * FROM table1 WHERE data_xml LIKE "%<text>%image%</text>%"; 

 

MySql Errors: none

 

Table structure:

CREATE TABLE `table1` (

`id` bigint(20) unsigned NOT NULL auto_increment,

`data_xml` text collate utf8_unicode_ci NOT NULL,

PRIMARY KEY  (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

 

Explain Output of query:

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra

1SIMPLEtable1ALLNULLNULLNULLNULL2Using where

 

Description:

I want this query to return the records which contain some string enclosed within <text> and </text> sub-strings. The string to be searched comes from php variable.

 

My problem:

Consider this data:

 

data_xml

================================================

<text>hello world</text><image>1</image><text>how are you</text>

================================================

 

string to be searched = "image"

 

The query shows this record in result, although "image" is not enclosed in immediate <text> & </text>.

The pattern search is greedy. How can make it otherwise?

 

What I've tried:

I looked for regular expressions, some inline options that are used in PHP, but they are not supported by MySQL.

 

 

[vikram8jp]

SELECT * FROM table1 WHERE data_xml REGEXP "<text>.*([^</]image).*</text>"; 

'

 

This is the query I really wanted.