CODEWITHSUNDEEP
mysqlsqldatabase
draw134
draw134

Reputation: 1187

How do I split every data or string that has a space on it using MySQL?

I wrote a query which is this:

SELECT mrt_name as MRT ,
operation_alpha_numeric_codes as Original,
SUBSTRING_INDEX(operation_alpha_numeric_codes,' ', 1) as First_code,
SUBSTRING_INDEX(operation_alpha_numeric_codes,' ', -1) as Second_Code,
SUBSTRING_INDEX(operation_alpha_numeric_codes,' ', -2) as Third_Code 
FROM scraping.xp_pn_mrt;

I got result like this

enter image description here

As you can see the second_code copies the value of the original or the first_code if the value doesn't have a corresponding space or data. Also, the third code gets the second_code in the records that have a third code in them. How do I prevent the data being copied or set it to blank when the code doesn't have a corresponding value in it and how can I achieve getting the third code without copying the second one? Can someone help me with my query and what's wrong with it? Thanks a lot.

Upvotes: 0

Views: 163

Answers (3)

wookiekim
wookiekim

Reputation: 1176

You can try the following:

SELECT 
   mrt_name as MRT ,
   operation_alpha_numeric_codes as Original,
   SUBSTRING_INDEX(SUBSTRING_INDEX(operation_alpha_numeric_codes , ' ', 1), ' ', -1) AS First_code,
   If(  length(operation_alpha_numeric_codes ) - length(replace(operation_alpha_numeric_codes , ' ', ''))>=1,  
       SUBSTRING_INDEX(SUBSTRING_INDEX(operation_alpha_numeric_codes , ' ', 2), ' ', -1) , '') 
           as Second_code,
   If(  length(operation_alpha_numeric_codes ) - length(replace(operation_alpha_numeric_codes , ' ', ''))>=2,
       SUBSTRING_INDEX(SUBSTRING_INDEX(operation_alpha_numeric_codes , ' ', 3), ' ', -1), '') 
           AS Third_code
FROM scraping.xp_pn_mrt;

[EDIT]

For double spaces between each value, This will work:

SELECT 
   mrt_name as MRT ,
   operation_alpha_numeric_codes as Original,
If(  length(operation_alpha_numeric_codes ) - length(replace(operation_alpha_numeric_codes , ' ', ''))>=2,  
       SUBSTRING_INDEX(SUBSTRING_INDEX(operation_alpha_numeric_codes , ' ', 3), ' ', -1) , '') 
           as Second_code,
   If(  length(operation_alpha_numeric_codes ) - length(replace(operation_alpha_numeric_codes , ' ', ''))>=4,
       SUBSTRING_INDEX(SUBSTRING_INDEX(operation_alpha_numeric_codes , ' ', 3), ' ', -1), '') AS Third_code
FROM scraping.xp_pn_mrt;

Upvotes: 2

Nick
Nick

Reputation: 147196

Since you're using MariaDB, you can use REGEXP_REPLACE to extract the parts of the code that you want:

SELECT 
  operation_alpha_numeric_codes as Original,
  REGEXP_REPLACE(operation_alpha_numeric_codes, '^([^ ]+)(?: ([^ ]+))?(?: ([^ ]+))?$', '\\1') as First_code,
  REGEXP_REPLACE(operation_alpha_numeric_codes, '^([^ ]+)(?: ([^ ]+))?(?: ([^ ]+))?$', '\\2') as Second_code,
  REGEXP_REPLACE(operation_alpha_numeric_codes, '^([^ ]+)(?: ([^ ]+))?(?: ([^ ]+))?$', '\\3') as Third_code
FROM data

Output for (part of) your sample data

Original        First_code  Second_code     Third_code
NS23            NS23        
NS24 NE6 CC1    NS24        NE6             CC1
NS25 EW13       NS25        EW13

Demo on dbfiddle

Here's a version that will also work on MySQL 5.7, using RLIKE to check if the input matches given patterns:

SELECT 
  operation_alpha_numeric_codes as Original,
  SUBSTRING_INDEX(operation_alpha_numeric_codes, ' ', 1) AS First_code,
  CASE WHEN operation_alpha_numeric_codes RLIKE '^([^ ]+)$' THEN ''
       WHEN operation_alpha_numeric_codes RLIKE '^([^ ]+)( ([^ ]+))?$' THEN SUBSTRING_INDEX(operation_alpha_numeric_codes, ' ', -1)
       ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(operation_alpha_numeric_codes, ' ', 2), ' ', -1)
  END AS Second_code,
  CASE WHEN operation_alpha_numeric_codes RLIKE '^([^ ]+)( ([^ ]+)){2}$' THEN SUBSTRING_INDEX(operation_alpha_numeric_codes, ' ', -1)
       ELSE ''
  END AS Third_code
FROM data

Demo on dbfiddle

Upvotes: 5

VBoka
VBoka

Reputation: 9083

I have used CASE WHEN clause with LENGTH function. I used LENGTH to calculate number of occurences of your separator ' ' in the string. CASE WHEN it is ONE occurances then there are TWO "results". CASE WHEN it is TWO occurances then there are THREE "results".

Here is the DEMO that will show the correct results for your two problematic data.

SELECT mrt_name as MRT 
   , operation_alpha_numeric_codes as Original
   , SUBSTRING_INDEX(operation_alpha_numeric_codes,' ', 1) as First_code
   , CASE WHEN LENGTH(operation_alpha_numeric_codes) - LENGTH(REPLACE(operation_alpha_numeric_codes, ' ', '')) = 1 
          THEN SUBSTRING_INDEX(operation_alpha_numeric_codes,' ', -1)
          WHEN LENGTH(operation_alpha_numeric_codes) - LENGTH(REPLACE(operation_alpha_numeric_codes, ' ', '')) = 2
          THEN SUBSTRING_INDEX(SUBSTRING_INDEX(operation_alpha_numeric_codes,' ', 2), ' ', -1)
          ELSE NULL
     END Second_Code
   , CASE WHEN LENGTH(operation_alpha_numeric_codes) - LENGTH(REPLACE(operation_alpha_numeric_codes, ' ', '')) = 2 
          THEN SUBSTRING_INDEX(SUBSTRING_INDEX(operation_alpha_numeric_codes,' ', -2), ' ', -1)
          ELSE NULL
     END Third_Code
FROM scraping.xp_pn_mrt;

Upvotes: 1

Related Questions