Contains string in a list by sql

Question

I use mysql 5.1.72-community.

I have a table named team:

team_id          team_name
    1             US
    1             CH
    2             AD
    2             JP
    2             IT
    3             AU
    3             CL
    3             CU

and a table named person:

person_id        address
    10051        US. Idaho
    10152        US. Connecticut
    10053        CH. Aargau
    10054        CH. Bern
    10055        JP. Tokyo
    10056        JP. Ginza
    10057        Uneo of JP

Now I want create a view named person_view, like:

person_id     address        team_id
10051        US. Idaho          1
10152        US. Connecticut    1
10053        CH. Aargau         1
10054        CH. Bern           1
10055        JP. Tokyo          2
10056        JP. Ginza          2
10057        Uneo of JP         2

That means if person.address contains the team.team_name, then person_view.team_id = team.team_id.

How can I do this please?

@jarlh Thank you. that is an old project, have no person.team_id. I just want to do that by a view. — xunitc
– xunitc, Commented Jan 24, 2018 at 10:44
@xunitc Is the team_name in the adress column always on the left or right side? Or can it be anywhere in the adress column? — ppijnenburg
– ppijnenburg, Commented Jan 24, 2018 at 10:49
@ppijnenburg i didnt see 10057 at the time of posting thanks for pointing that out, i have deleted my answer — WhatsThePoint
– WhatsThePoint, Commented Jan 24, 2018 at 10:50
@ppijnenburg it can be anywhere in the address. Thank you, I see your first answer, it find some rows except 10057. — xunitc
– xunitc, Commented Jan 24, 2018 at 11:00

Justinas Marozas · Accepted Answer · 2018-01-24 11:06:23Z

1

Have the team_id column in persons table if you can and join on it. If you can't - you can try doing this:

CREATE VIEW person_view
AS
SELECT p.person_id,
    t.address,  
    t.team_id
FROM person p 
-- if team name can be anywhere in the address:
JOIN team t ON p.address LIKE CONCAT('%', t.team_name, '%');
-- -- if team name will always be in front of address:
-- JOIN team t ON p.address LIKE CONCAT(t.team_name, '%');

edited Jan 24, 2018 at 11:06

answered Jan 24, 2018 at 10:49

Justinas Marozas

2,7321 gold badge21 silver badges40 bronze badges

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

2 Comments

xunitc Over a year ago

Thank you. I try it in mysql 5.1.72-community, but it can not run. I just copy it and run. what is my wrong please?

Justinas Marozas Over a year ago

@xunitc , my bad. I've updated code to use CONCAT instead of + now.

Muhammad Rehan Qadri · Accepted Answer · 2018-01-24 10:47:10Z

0

Try this (sorry I can't test it):

SELECT p.person_id, p.address, t.team_id
FROM team t, person p
WHERE t.team_name like p.address

answered Jan 24, 2018 at 10:47

Muhammad Rehan Qadri

7,8541 gold badge19 silver badges19 bronze badges

3 Comments

xunitc Over a year ago

Thank you, but it can not work for me, maybe I have some mistakes.

Muhammad Rehan Qadri Over a year ago

check column names, table names spells

xunitc Over a year ago

Thank you, and that need p.address like t.team_name, and % needed too.

codeLover · Accepted Answer · 2018-01-24 11:09:40Z

0

CREATE OR REPLACE VIEW person_view
AS
SELECT p.person_id,
    t.address,  
    t.team_id
FROM person p ,
team t where p.address LIKE '%' || t.team_name || '%';

Try this

answered Jan 24, 2018 at 11:09

codeLover

2,5921 gold badge13 silver badges29 bronze badges

3 Comments

xunitc Over a year ago

Thank you, but the view is too large by this sql. maybe Cartesian product.

codeLover Over a year ago

Is it containing duplicates?

xunitc Over a year ago

Yes. it will put 10053,"CH. Aargau" to team_id 1,2,3, and term_id 1 twice and so on.

Collectives™ on Stack Overflow

Contains string in a list by sql

3 Answers 3

2 Comments

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related