regex for this string in mysql

Question

I have these rows:

db666405.gallery
db666405.table1
db666405.table2

I want to capture the word after the dot.

How to do with regex in mysql?

I have tried with ^\. or [.], but I did not succeed.

SELECT *
FROM `table`
WHERE `column` REGEXP '^\\.'

so you only want to capture the value preceeding .gallery? — m_callens
– m_callens, Commented Jan 26, 2017 at 18:15

Bohemian · Accepted Answer · 2017-01-26 18:41:59Z

2

MySQL regex doesn't "capture" anything. It's for matching only; there is no support for regex replacement in MySQL.

I assume you want to return the part after the dot:

select *, substring(`column`, instr(`column`, '.') + 1) as ext
from `table`
where `column` like '%.?%'

answered Jan 26, 2017 at 18:41

Bohemian♦

427k103 gold badges604 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

chris85 Over a year ago

How does instr differ from locate? Is one more backward/foward compatible?

Rick James Over a year ago

Compatible in MySQL versions? They probably exist in all versions. Compatible with other RDBMS products? I doubt it; cross-vendor compatibility anywhere in SQL is an unfulfilled dream.

chris85 · Accepted Answer · 2017-01-26 19:35:13Z

0

To select any columns that have the word Gallery, you can use a

SELECT *
FROM `table`
WHERE `column` LIKE '%gallery%' `

This will return all that have gallery

edited Jan 26, 2017 at 19:35

chris85

23.9k7 gold badges37 silver badges51 bronze badges

answered Jan 26, 2017 at 18:33

pfg

3,8271 gold badge26 silver badges46 bronze badges

4 Comments

user7449991 Over a year ago

sorry this is only an example but i have more row that the word gallery change like this db666405.gallery, db666405.table1, db666405.table2 . i want to capture the word after the dot

pfg Over a year ago

YOu can use a site such as regex101 to test regexes. ^.+\.(.+)$ will work because the parenthesis say to start after the period on what is captured

chris85 Over a year ago

You can't capture in mysql.

user7449991 Over a year ago

^.+\.(.+)$ It works with PHP but with MySQL does not work $re = '/^.+\.(.+)$/'; <br> $str = 'db666405.gallery'; preg_matchl($re, $str, $matches); // Print the entire match result print_r($matches);

Collectives™ on Stack Overflow

regex for this string in mysql

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related