CODEWITHSUNDEEP
mysql
Ababneh A
Ababneh A

Reputation: 1134

Finding strings that have common words in mysql

I am using mysql, I have 2 tables 

emp(id int, name varchar, dept_id int)
dept(id int, name varchar)

I would like to get the employees with the following restriction: The employee name has at least 1 common word with his/her department name.

The solution can be a select statement or a stored procedure? However any solution is welcome.

Upvotes: 1

Views: 776

Answers (2)

valex
valex

Reputation: 24144

Use REGEXP and create a search string from emp.name. Here is the SQLFiddle demo

select emp.id,
       emp.name emp_name, 
       dept.name dep_name from emp
join dept
where emp.dept_id=dept.id
      AND

      dept.name regexp 
        CONCAT('([[:<:]]',
               REPLACE(
                   TRIM(emp.name),
                   ' ',
                   '[[:>:]]|[[:<:]]'
               ),
               '[[:>:]])');

Upvotes: 1

fancyPants
fancyPants

Reputation: 51908

SELECT
*
FROM
emp
INNER JOIN dept ON emp.dept_id = dept.id
WHERE
emp.name LIKE CONCAT('%', dept.name, '%')

This will get you all employees whose name is in their department name. However if employee name column is prename and surname, this is bad database design and it's a bit harder to implement the "at least one common word" restriction. Either change DB schema by separating name into two columns or you will have to use the functions

  • substring()
  • substring_index()
  • length()

to split the name and so on. This will get ugly. Read more about those functions here.

Anyway, have a try yourself or you won't learn it. This is not a "do all the work for me" site and there are more people willing to help when you at least show some research effort. Good luck.

Upvotes: 0

Related Questions