substring_index() returns wrong result in MySQL

Question

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?

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, — P.Salmon
– P.Salmon, Commented Nov 9, 2021 at 8:39
Your hangar locations contain a whitespace after the delimiter, so of course the result of substring_index is ' NSW' — derpirscher
– derpirscher, Commented Nov 9, 2021 at 8:43
Thank you @P.Salmon !! Fix that and it works like a charm :) — JaFranke
– JaFranke, Commented Nov 9, 2021 at 8:47
@derpirscher Your answer is similar to P.Salmon. Thank you very much for pointing that out!! It works now :D — JaFranke
– JaFranke, Commented Nov 9, 2021 at 8:48

P.Salmon · Accepted Answer · 2021-11-09 08:41:20Z

1

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

answered Nov 9, 2021 at 8:41

P.Salmon

17.7k2 gold badges14 silver badges22 bronze badges

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

Comments

ekochergin · Accepted Answer · 2021-11-09 08:39:09Z

1

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';

answered Nov 9, 2021 at 8:39

ekochergin

4,1592 gold badges14 silver badges20 bronze badges

1 Comment

JaFranke Over a year ago

I've tried both of the codes and they work perfectly! This is another way to solve the problem! Thank you very much :)

Collectives™ on Stack Overflow

substring_index() returns wrong result in MySQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related