Using Columns in a Regexp in MySQL

Using Columns in a RegExp in MySQL

You're searching for the literal string a.company, and not the column. Try this:

SELECT a.id, a.company, a.name, b.title, b.description, b.t_id
FROM a, b
WHERE 
    ( 
        b.title REGEXP concat('[[:<:]]', a.company, '[[:>:]]') 
        OR b.description REGEXP concat('[[:<:]]', a.company, '[[:>:]]') 
        OR b.title REGEXP concat('[[:<:]]', a.name, '[[:>:]]')
        OR b.description REGEXP concat('[[:<:]]', a.name, '[[:>:]]')
    ) 
    AND a.company !=  '' AND a.name !=  ''

This provides the regexp with the value of the column, not the string 'a.company'. Since my guess is that you want to compare the column value (and not the column name), you will need to concatenate your regexp together.

You can test this with this query:

select
    'My col: a.company' as Test1,
    'My col: ' + a.company as Test2
from
    a

Here, Test1 will always be the value My col: a.company, while Test2 will be My col: <company col value here>.

Selecting part of a field with a regex

This answer is obsolete. MySql has better regex support.

No, sad to say MySQL doesn't have a way to apply a regex to a column's contents in a SELECT clause, only a WHERE clause.

But you can use ordinary (non-regex) string manipulation functions to do this. If the column containing your ampersand-separated parameter string is named url, you can get the id number with this fine string expression, which finds your id number.

  CAST(RIGHT(url, LENGTH(url) - 3 - LOCATE('&id=', url)) AS SIGNED INTEGER)

So, if you want a list of id values from the url columns of table1, you could use this SELECT query.

SELECT CAST(RIGHT(url, LENGTH(url) - 3 - 
                       LOCATE('&id=', url)) AS SIGNED INTEGER) AS id
  FROM table1
 WHERE url REGEXP '&id=[0-9]+'

As you can see this uses the regexp search function to locate the appropriate rows.

There is nothing fast about this. Regexp matching can't exploit a MySQL index. If you have the choice of loading your table with the id column pre-extracted you'll be much better off searching when your table gets big.

Using Regular Expressions in MySql

You have to concat the hole reg string like

CREATE TABLE Table1
    (`first_name` varchar(7), `second_name` varchar(9), `attribute` varchar(66))
;
    
INSERT INTO Table1
    (`first_name`, `second_name`, `attribute`)
VALUES
    ('Vicenta', 'Kravitz', '0%Vicenta_Kravitz%'),
    ('Shayne', 'Dahlquist', '0R0V331K8Q7ypBi4Az3B6Nm0jCqUk%Shayne_Dahlquist%46E3O0u7t7'),
    ('Mikel', 'Kravitz', 'PBX86iw1Ied87Z9OarE6sdSLdt%Mikel_Kravitz%W73XOY9YaOgi060r2x12D2EmD')
;

    SELECT first_name,
       second_name,
       attribute
FROM Table1
WHERE attribute REGEXP  CONCAT('^.+%',binary(first_name),'_',binary(second_name),'%.*')
ORDER BY attribute; 

first_name | second_name | attribute                                                         
:--------- | :---------- | :-----------------------------------------------------------------
Vicenta    | Kravitz     | 0%Vicenta_Kravitz%                                                
Shayne     | Dahlquist   | 0R0V331K8Q7ypBi4Az3B6Nm0jCqUk%Shayne_Dahlquist%46E3O0u7t7         
Mikel      | Kravitz     | PBX86iw1Ied87Z9OarE6sdSLdt%Mikel_Kravitz%W73XOY9YaOgi060r2x12D2EmD

db<>fiddle here

MySQL regex searching on a column

MySQL's REGEXP has its own particular syntax. It doesn't support \s, \d, or \w, but we can still approximate these. Here is a version of your pattern which seems to work:

SELECT vehicle_plate_number 
FROM users 
WHERE vehicle_plate_number REGEXP '[a-z0-9].*[[:space:]]Metro[[:space:]][0-9]{2}[-][0-9]{4}';

Demo

Mysql treat column value as a regex for select

Change blue || spots to blue|spots to make it a correct regular expression.

Then this should work:

ON B.fur REGEXP CONCAT('^(', A.fur, ')$')

I added ^ and $ in order to anchor to the ends. That way, blue will match, but blueblood will not.

I added ( and ) so that blue|sports would match only blue and sports, not blueblood and teamsports. Think about what happens with ^blue|sports$ -- that means "start with blue or end with sports".

Multiple Column REGEX search in MySQL

If you want to regexp search a value in multiple columns then you can do:

SELECT * FROM table where CONCAT(col1, col2, col3) REGEXP 'search-pattern';

Using regular expressions to split one column into multiple columns

Update — September 2021

This is now possible.

MySQL 8.0 as well as MariaDB 10.0.5 introduced support for the REGEXP_SUBSTR function, which applies a regex on an input string, and returns the matching part (or NULL if there was no match). This is much more powerful and flexible that the former SUBSTRING_INDEX approach.

Using this function in MySQL, the original problem can be resolved with the following query:

SELECT regexp_substr(myColumn, '[A]+') AS `A`, 
       regexp_substr(myColumn, '[B]+') AS `B`,
       regexp_substr(myColumn, '[C]+') AS `C`
  FROM ...  

Side note: Capturing Nth matching occurence

MySQL's REGEXP_SUBSTR accepts a third argument, indicating the occurence number to be returned. For example, to return the third word in the input string, one could write: regexp_substr(myColumn, '[a-z]+', 3). This is however not supported by MariaDB.

The same can be achieved using REGEXP_REPLACE by expanding the original pattern to a sequence of capture groups. For example, one could use the following regex to capture the third word of the input string:

regexp_replace(myColumn, '^([a-z]+) ([a-z]+) ([a-z]+).*$', '\\3')

Original answer

This can't be done using regexes in MySQL. Unfortunately, MySQL support regexes only as a boolean condition (notably in where clauses), but not to extract nor alter the content of a string.

You may however achieve what you described entirely from a MySQL query, using substring_index. Here is an example usage for your scenario.

SELECT substring_index(substring_index(myColumn, ' ', 1), ' ', -1) AS `A`, 
       substring_index(substring_index(myColumn, ' ', 2), ' ', -1) AS `B`,
       substring_index(                myColumn         , ' ', -1) AS `C`
  FROM ...  

Alternatively, if you must absolutely use regexes, then you might pipe MySQL's output to some regex engine. Ask me if you need more info about this strategy.

---

Related Topics