Use of REGEXP_SUBSTR methods in Oracle

Recently in the business travel ticket platform, encountered such a problem:
There is a tt_ticket table that is used to store ticket information. Inside there is a field called schedule, which represents the itinerary, the storage format is: Beijing/Hongqiao
As the company is located in Shanghai. Shanghai now has two airports: Pudong and Hongqiao. Therefore, for the departure or arrival of the Shanghai Air ticket, the itinerary will be stored in Hongqiao or Pudong, of course. In some cases, it may also be directly stored in Shanghai (very unlikely.) But it doesn't mean no). In this way, the itinerary is for the trip from Shanghai. There may be several possibilities:
A. Hongqiao/Beijing
B. Pudong/Beijing
C. SHANGHAI/Beijing
Now to achieve the search for the departure city for Shanghai, the three information all pulled out.
First, create an urban area mapping table Tt_ticket_city_mapper, including the Fields city and Mapper_city, to store the following data:

City

	mapper_city
Hongqiao	Shanghai
Pudong	Shanghai
Shanghai	Shanghai

The following first put SQL out, is part of the code in the MyBatis:

      Select* fromTt_ticketwhere 1=1<iftest="Departurecity! =" and departurecity!=null "><! [Cdata[and (NVL (SelectM.mapper_city fromTt_ticket_city_mapper mwhereM.city = Trim ((SelectRegexp_substr (T.schedule,' [^/]+ ',1,1) fromDual)), Trim ((SelectRegexp_substr (T.schedule,' [^/]+ ',1,1) fromDual)) = NVL ((SelectM.mapper_city fromTt_ticket_city_mapper mwhereM.city = Trim (#{departurecity})),#{departurecity}))]]></if><iftest="Arrivecity! =" and arrivecity!=null "> <! [Cdata[and (NVL (SelectM.mapper_city fromTt_ticket_city_mapper mwhereM.city = Trim ((SelectRegexp_substr (T.schedule,' [^/]+ ',1,2) fromDual)), Trim ((SelectRegexp_substr (T.schedule,' [^/]+ ',1,2) fromDual)) = NVL ((SelectM.mapper_city fromTt_ticket_city_mapper mwhereM.city = Trim (#{arrivecity})),#{arrivecity})]]></if>

Explain first: Select Regexp_substr (t.schedule, ' [^/]+ ', ') ' from dual
Example:
Select Regexp_substr (' Hongqiao/Beijing ', ' [^/]+ ', ') from dual
The return is Hongqiao
Select Regexp_substr (' Hongqiao/Beijing ', ' [^/]+ ') from dual
Returned is the Beijing
The first parameter is the string to parse, the second is the expression, and the third represents the starting position of the string resolution. Example:
Select Regexp_substr (' Hongqiao/Beijing ', ' [^/]+ ', 2,1) from dual
The bridge is returned.
The fourth parameter represents the first value in the array after the split.
Such words
Nvl
(Select M.mapper_city
From Tt_ticket_city_mapper m
where m.city = Trim ((select Regexp_substr (t.schedule, ' [^/]+ ',) from dual)]
) ,
Trim ((select Regexp_substr (t.schedule, ' [^/]+ ', ") from dual)]
)
will be able to take out/before the string, such as Pudong or Hongqiao, and then query the Tt_ticket_city_mapper in the map of the city of Shanghai. Assuming it's not what it is.
The equal number behind the same is to do a conversion.

Of course, seeing this, there are certain people who say why not just save the city and reach the city two fields? That is because the itinerary may be Beijing/Hong Kong/Miami/Hong Kong/Beijing this data.

How to deal with this kind of data. Not yet, just a glance at the Internet. It should be easy to use the stored procedure inside the operation
Is there any way we can share it?

Use of REGEXP_SUBSTR methods in Oracle