Mysql regexp to get right table names from database schema

Question

I'm trying to select, from mysql, some specific tables that matches a pattern.

The specific pattern I'm looking for is like 'my_table_number'. For example, my_table_436814 or my_table_35413. The thing is that there are other tables that looks like my_table_old_14353 or my_table_351434_times. I just want to filter out the ones that are my_table_number.

I have tried different patterns, but failed to get what I really need.

The most closest approach was with this:

select table_name from 
information_schema.columns ca 
where ca.table_name REGEXP '[0-9]$'

How can I achieve what I'm looking for?

Is `my_table` a fixed string? – Wiktor Stribiżew Feb 03 '21 at 22:54 — Wiktor Stribiżew, Feb 03 '21 at 22:54
Ok, then the answer below is doing what you need. – Wiktor Stribiżew Feb 03 '21 at 23:07 — Wiktor Stribiżew, Feb 03 '21 at 23:07

score 2 · Accepted Answer · answered Feb 03 '21 at 22:56

2

Use

REGEXP '^my_table_[0-9]+$'

See proof

NODE	EXPLANATION
`^`	the beginning of the string
`my_table_`	'my_table_'
`[0-9]+`	any character of: '0' to '9' (1 or more times (matching the most amount possible))
`$`	before an optional \n, and the end of the string

answered Feb 03 '21 at 22:56

Ryszard Czech

18,032
4
24
37

This actually solved the problem. Thank you, very much! One las thing, this works with and without the `^` symbol. Why? – xerac Feb 03 '21 at 23:08
1

@xerac If you do not want to ensure the match happens to be at the beginning of the string, you do not need `^`. – Ryszard Czech Feb 03 '21 at 23:26

Mysql regexp to get right table names from database schema

1 Answers1