CODEWITHSUNDEEP
mysqlregex
Uchenna Nwanyanwu
Uchenna Nwanyanwu

Reputation: 3204

Pattern matching in MySQL database query

I have a column named id in two different tables, table1 and table2 which always contain 30 characters. I want to select from table1 and table2 where the 6th to 30th character of the id column in both tables are the same. The id column is unique in both tables.

Upvotes: 0

Views: 63

Answers (3)

juergen d
juergen d

Reputation: 204746

select * 
from table1
join table2 on substring(table1.id, 6) = substring(table2.id, 6)

Upvotes: 2

Sadikhasan
Sadikhasan

Reputation: 18600

Try this

SELECT *
FROM table1
JOIN table2 ON SUBSTRING(table1.id,7) = SUBSTRING(table2.id,7);

Upvotes: 0

Arth
Arth

Reputation: 13110

You can do this without regex, and I like the right function for this (returns n rightmost characters):

SELECT *
  FROM table1 t1
  JOIN table2 t2
    ON RIGHT(t1.id,25) = RIGHT(t2.id,25)

Actually on second thought the SUBSTRING way is probably better in this instance, just in case a rows end up with shorter ids.

Upvotes: 2

Related Questions