问题描述:

I have a the following table structure

FirstName Surname

Joe Jay Blog Pepper

Jane Jay Bibs Salt

Jim Jack Jones Snr Sugar

What I am trying to do is take the value in first name and take everything before the first space into "first", everything between the first and second spaces and move it to "middle" and everything after the 3rd space in "end".

So for my last record:

first = Jim

second = Jack

third = Jones Snr

I can satisfy my first requirement with the following below:

SELECT SUBSTR(FirstName, 1,LOCATE(' ',FirstName)) AS first

But that seems to be as fair as I am currently.

Thanks!

网友答案:

This seems to work :P

First get firstname and group the middle+lastname, and just do the same again with the result to get the middlename with LEFT and lastname with right.

SELECT
  name, 
  firstname, /*already got this one*/
  LEFT(middle_last_name, INSTR(middle_last_name, ' ')-1) AS middlename, /*do the same LEFT to get middlename*/
  RIGHT(middle_last_name, LENGTH(middle_last_name)-INSTR(middle_last_name, ' ')) AS last_name /*get the remaining chars for lastname*/
FROM (

    SELECT *, 
    LEFT(name, INSTR(name, ' ')-1) AS firstname, /*Simply get the firstname with left*/
    RIGHT(name, LENGTH(name)-INSTR(name, ' ')) AS middle_last_name /*group the rest for this sub-query*/
    FROM users
    );
相关阅读:
Top