Hello again, everyone. Every time WE meet, IT means I’ve solved another problem. Today, this problem is more ruthless, and let my SQL ability has further improved. Simply say I I recently why there are so many SQL problems, the main reason is that we synchronize data from the hive database in the data center, and their database data is too dirty, there are all kinds of data you can not imagine, and we can not push them, not the way can only be processed by SQL themselves.

Here’s today’s weird question. The data we have synchronized is as follows.

The scariest thing is to add some extra numbers before the type. These contents are completely meaningless to me, I just need the content after the stop sign. So what should I do to get the rest of this. The first thing that came to mind was split. But after checking, mysql doesn’t support the split function. I was really disappointed. Later, I had to look for a similar function and found that there is an INSTR() function that can be used to determine whether the period contains a string and return the index containing the string. This problem can be solved perfectly when combined with the SUBSTR function for interception.

Also note that it is a good idea to intercept only if the field contains the string and not if it does not. The resulting SQL looks like this:

select 
year.case when INSTR(subtype.', ') >0 then SUBSTR(subtype.INSTR(subtype.', ') +1)
else subtype END subtype
from 
xxx_table_name
Copy the code

The result was success.