Find a specific sub-string inside MySQL field

Question

I have a database with 1 column that has text fields. I need to find and mount a new column with a specific substring. My English isn't good, so I'll try to explain below.

Example of 1 field:

Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed diam nonummy nibh euismod tincidunt ut laoreet dolore magna aliquam erat volutpat. Ut wisi enim ad minim veniam, quis nostrud exerci tation ullamcorper suscipit lobortis nisl ut aliquip ex ea commodo consequat. Duis autem vel eum iriure dolor in hendrerit in vulputate velit esse molestie consequat, vel illum dolore eu feugiat nulla facilisis at XXXX : yyyyy vero eros et accumsan et iusto odio dignissim qui blandit praesent luptatum zzril delenit augue duis dolore te feugait nulla facilisi. Nam liber tempor cum soluta nobis eleifend option congue nihil imperdiet doming id quod mazim placerat facer possim assum. Typi non habent claritatem insitam; est usus legentis in iis qui facit eorum claritatem. Investigationes demonstraverunt lectores legere me lius quod ii legunt saepius. Claritas est etiam processus dynamicus, qui sequitur mutationem consuetudium lectorum. Mirum est notare quam littera gothica, quam nunc putamus parum claram, anteposuerit litterarum formas humanitatis per seacula quarta decima et quinta decima. Eodem modo typi, qui nunc nobis videntur parum clari, fiant sollemnes in futurum.

I need to find and extract the value of yyyyy that comes after xxxx :, and the position of the variable inside the text. So far, I've tried this:

SELECT 
 SUBSTRING_INDEX(
 RIGHT(description, 
 GREATEST(LOCATE('xxxx :', description),
 LOCATE('xxxx :', description))-1), ' ', -1)
FROM wwwww

I know that may be the way to solve it, but I can't make this works right. (I've looked at this already: Return specific text from MySQL using LOCATE and SUBSTRING)

PS: The "XXXX :" its always the same... the yyyyy has a variable value

Answer 1

please try this

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(description,'XXXX : ',-1),' ',1) from wwwww

here's the sqlFiddle

not sure why it isn't working for you maybe try this variation see if this works

SELECT SUBSTRING_INDEX(RIGHT(post_content,LENGTH(post_content) - (LOCATE('XXXX : ',post_content)+6)),' ',1) as var from wp_posts

Answer 2

To find the first word (or a string) that follows xxxx : (possibly with and without spaces around :) in a block of text using PHP and regex:

$subject = "Lorem ipsum dolor sit amet, consectetuer adipiscing elit, vel illum dolore eu feugiat nulla facilisis at XXXX : yyyyy vero eros et accumsan et iusto odio dignissim qui blandit praesent luptatum zzril delenit augue duis dolore te feugait nulla facilisi.";

# Case insensitive match.
$pattern = '/XXXX\s?:\s?(\w+)/i';

# If `XXXX` really has to be all caps, remove the `i` flag, like so:
#$pattern = '/XXXX\s?:\s?(\w+)/';

preg_match($pattern, $subject, $matches, PREG_OFFSET_CAPTURE);
print_r($matches[1]);

$matched_text = $matches[1][0];
echo $matched_text;

$match_position = $matches[1][1];
echo $match_position;