substring_index() returns wrong result in MySQL-CodePudding

I've encountered a wrong return when using substring_index.
You can reproduce my data using the query below:

create schema Airport;
use Airport;


create table aircrafttype(
aircrafttypeid char(2),
aircrafttypename varchar(20),
primary key(aircrafttypeid));

create table aircraft(
aircraftid char(2),
aircraftpurdate date,
aircraftseatcap numeric(3),
aircrafttypeid char(2),
primary key(aircraftid),
foreign key(aircrafttypeid) references aircrafttype(aircrafttypeid));

create table hangar(
hangarid char(2),
hangarlocation varchar(20),
hangarstoragecap numeric(2),
primary key(hangarid));

create table serviceteam(
teamid char(2),
teamname varchar(20),
teamlevel numeric(1),
primary key(teamid));

create table service(
serviceid char(3),
servicedate date,
hangarid char(2),
aircraftid char(2),
teamid char(2),
primary key(serviceid),
foreign key(hangarid) references hangar(hangarid),
foreign key(aircraftid) references aircraft(aircraftid),
foreign key(teamid) references serviceteam(teamid));


insert into aircrafttype values ('B7','Boeing 777');
insert into aircrafttype values ('B3','Boeing 737');
insert into aircrafttype values ('B8','Boeing 787');
insert into aircrafttype values ('B6','Boeing 767');
insert into aircrafttype values ('22','Airbus 220');
insert into aircrafttype values ('31','Airbus 310');

insert into aircraft values('A1','2012-06-19',140,'B3');
insert into aircraft values('A2','2013-08-14',129,'B6');
insert into aircraft values('A3','2013-05-01',104,'B3');
insert into aircraft values('A4','2017-04-19',296,'B7');
insert into aircraft values('A5','2018-03-02',120,'B6');
insert into aircraft values('A6','2014-10-19',191,'31');
insert into aircraft values('A7','2015-10-03',198,'31');
insert into aircraft values('A8','2016-12-31',204,'22');
insert into aircraft values('A9','2017-01-01',173,'22');


insert into hangar values('H1','Sydney, NSW',7);
insert into hangar values('H2','Melbourne, VIC',22);
insert into hangar values('H3','Sydney, NSW',25);
insert into hangar values('H4','Brisbane, QLD',8);
insert into hangar values('H5','Launceston, TAS',14);

insert into serviceteam values('T1','Sydney Rabbitohs',5);
insert into serviceteam values('T2','Melbourne Rebels',3);
insert into serviceteam values('T3','Queensland Reds',5);
insert into serviceteam values('T4','Team TRU',4);

insert into service values('S1','2019-09-25','H3','A3','T1');
insert into service values('S2','2019-08-27','H2','A7','T2');
insert into service values('S3','2019-09-22','H5','A7','T4');
insert into service values('S4','2019-05-13','H5','A4','T4');
insert into service values('S5','2019-01-08','H1','A4','T1');
insert into service values('S6','2019-09-07','H4','A9','T3');
insert into service values('S7','2019-12-20','H3','A9','T1');
insert into service values('S8','2019-12-20','H4','A3','T3');
insert into service values('S9','2019-05-18','H4','A2','T3');
insert into service values('S10','2019-05-14','H3','A3','T1');
insert into service values('S11','2019-05-27','H3','A3','T1');
insert into service values('S12','2019-08-11','H3','A9','T1');
insert into service values('S13','2019-08-17','H4','A2','T3');
insert into service values('S14','2019-12-14','H4','A4','T3');
insert into service values('S15','2025-01-25','H5','A1','T4');

Now, I want to:

Print all details of the service if the aircraft has been serviced at any hangar in NSW.

So I tried using the substring_index but it gives me an empty return, which is not true. Here's the code that I used:

select s.*, hangarlocation
from service s, hangar h
where s.hangarid = h.hangarid
and SUBSTRING_INDEX(hangarlocation, ',', -1) = 'NSW';

Can someone help me point out what is wrong here?

CodePudding user response：

spaceNSW (which is what is returned by the substring index) is not the same as NSW either TRIM or include a space in the test

https://www.db-fiddle.com/f/jWBxcfXZJ8HnUuGTBKYtrt/0

CodePudding user response：

Assuming, location has always state name in the end, you might use "like" instead of substring

select s.*, hangarlocation
  from service s, 
       hangar h
 where s.hangarid = h.hangarid
   and hangarlocation like '%, NSW';

And it is recommended to use JOIN-syntax. This helps you to avoid mistakes like unexpected cross-joins and is more readable

select s.*, hangarlocation
  from service s
  join hangar h
    on s.hangarid = h.hangarid
 where hangarlocation like '%, NSW';