Podziel wartość z jednego pola na dwa

Mam pole tabeli, membernamektóre zawiera zarówno nazwisko, jak i imię użytkownika. Czy to możliwe, aby podzielić te na 2 pola memberfirst, memberlast?

Wszystkie rekordy mają format „Imię Nazwisko” (bez cudzysłowów i spacji pomiędzy).

Niestety MySQL nie posiada funkcji podziału łańcucha. Możesz jednak utworzyć w tym celu funkcję zdefiniowaną przez użytkownika, taką jak ta opisana w następującym artykule:

• Funkcja Split String MySQL autorstwa Federico Cargnelutti

Dzięki tej funkcji:

DELIMITER $$

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255) DETERMINISTIC
BEGIN 
    RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');
END$$

DELIMITER ;

możesz zbudować zapytanie w następujący sposób:

SELECT SPLIT_STR(membername, ' ', 1) as memberfirst,
       SPLIT_STR(membername, ' ', 2) as memberlast
FROM   users;

Jeśli wolisz nie używać funkcji zdefiniowanej przez użytkownika i nie masz nic przeciwko, aby zapytanie było nieco bardziej szczegółowe, możesz również wykonać następujące czynności:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(membername, ' ', 1), ' ', -1) as memberfirst,
       SUBSTRING_INDEX(SUBSTRING_INDEX(membername, ' ', 2), ' ', -1) as memberlast
FROM   users;

Wariant SELECT (bez tworzenia funkcji zdefiniowanej przez użytkownika):

SELECT IF(
        LOCATE(' ', `membername`) > 0,
        SUBSTRING(`membername`, 1, LOCATE(' ', `membername`) - 1),
        `membername`
    ) AS memberfirst,
    IF(
        LOCATE(' ', `membername`) > 0,
        SUBSTRING(`membername`, LOCATE(' ', `membername`) + 1),
        NULL
    ) AS memberlast
FROM `user`;

Takie podejście dba również o:

• wartości membername bez spacji : doda cały ciąg do memberfirst i ustawi element memberlast na NULL.
• wartości membername , które mają wiele spacji : doda wszystko przed pierwszą spacją do memberfirst, a resztę (w tym dodatkowe spacje) do memberlast.

Wersja UPDATE to:

UPDATE `user` SET
    `memberfirst` = IF(
        LOCATE(' ', `membername`) > 0,
        SUBSTRING(`membername`, 1, LOCATE(' ', `membername`) - 1),
        `membername`
    ),
    `memberlast` = IF(
        LOCATE(' ', `membername`) > 0,
        SUBSTRING(`membername`, LOCATE(' ', `membername`) + 1),
        NULL
    );

Wydaje się, że istniejące odpowiedzi są zbyt skomplikowane lub nie stanowią ścisłej odpowiedzi na dane pytanie.

Myślę, że prosta odpowiedź brzmi:

SELECT
    SUBSTRING_INDEX(`membername`, ' ', 1) AS `memberfirst`,
    SUBSTRING_INDEX(`membername`, ' ', -1) AS `memberlast`
;

Myślę, że w tej konkretnej sytuacji nie jest konieczne zajmowanie się nazwami składającymi się z więcej niż dwóch słów. Jeśli chcesz to zrobić poprawnie, w niektórych przypadkach rozłupywanie może być bardzo trudne lub nawet niemożliwe:

• Johann Sebastian Bach
• Johann Wolfgang von Goethe
• Edgar Allan Poe
• Jakob Ludwig Felix Mendelssohn-Bartholdy
• Petőfi Sándor
• 澤黒明

W odpowiednio zaprojektowanej bazie danych nazwiska ludzkie powinny być przechowywane zarówno w częściach, jak iw całości. Oczywiście nie zawsze jest to możliwe.

Jeśli plan jest zrobić to jako część zapytania, proszę nie robić ^(a) . Poważnie, to zabójca wydajności. Mogą wystąpić sytuacje, w których nie zależy Ci na wydajności (na przykład jednorazowe zadania migracji, aby podzielić pola, aby uzyskać lepszą wydajność w przyszłości), ale jeśli robisz to regularnie dla czegokolwiek innego niż baza danych myszy miki, marnujemy zasoby.

Jeśli kiedykolwiek będziesz musiał przetworzyć tylko część kolumny w jakiś sposób, twój projekt bazy danych jest wadliwy. Może dobrze działać w domowej książce adresowej, aplikacji z przepisami lub w dowolnej z wielu innych małych baz danych, ale nie da się jej skalować do „prawdziwych” systemów.

Przechowuj składniki nazwy w oddzielnych kolumnach. Łączenie kolumn za pomocą prostej konkatenacji (gdy potrzebujesz pełnego imienia i nazwiska) jest prawie zawsze o wiele szybsze niż dzielenie ich za pomocą wyszukiwania znakowego.

Jeśli z jakiegoś powodu nie możesz podzielić pola, przynajmniej umieść dodatkowe kolumny i użyj wyzwalacza wstawiania / aktualizowania, aby je wypełnić. Chociaż nie jest to 3NF, zagwarantuje to, że dane są nadal spójne i znacznie przyspieszy twoje zapytania. Możesz również upewnić się, że dodatkowe kolumny są pisane małymi literami (i indeksowane, jeśli ich szukasz) w tym samym czasie, aby nie musieć majstrować przy problemach ze wielkością liter.

A jeśli nie możesz nawet dodać kolumn i wyzwalaczy, pamiętaj (i uświadom swojego klienta, jeśli jest przeznaczony dla klienta), że nie jest skalowalny.

^(a) Oczywiście, jeśli zamierzasz użyć tego zapytania do naprawienia schematu, tak aby nazwy były umieszczane w oddzielnych kolumnach w tabeli, a nie w zapytaniu, uważam to za prawidłowe użycie. Ale powtarzam, robienie tego w zapytaniu nie jest dobrym pomysłem.

Użyj tego

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX( `membername` , ' ', 2 ),' ',1) AS b, 
SUBSTRING_INDEX(SUBSTRING_INDEX( `membername` , ' ', -1 ),' ',2) AS c FROM `users` WHERE `userid`='1'

Nie do końca odpowiadając na pytanie, ale napotkałem ten sam problem, który skończyłem:

UPDATE people_exit SET last_name = SUBSTRING_INDEX(fullname,' ',-1)
UPDATE people_exit SET middle_name = TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(fullname,last_name,1),' ',-2))
UPDATE people_exit SET middle_name = '' WHERE CHAR_LENGTH(middle_name)>3 
UPDATE people_exit SET first_name = SUBSTRING_INDEX(fullname,concat(middle_name,' ',last_name),1)
UPDATE people_exit SET first_name = middle_name WHERE first_name = ''
UPDATE people_exit SET middle_name = '' WHERE first_name = middle_name

W MySQL działa ta opcja:

SELECT Substring(nameandsurname, 1, Locate(' ', nameandsurname) - 1) AS 
       firstname, 
       Substring(nameandsurname, Locate(' ', nameandsurname) + 1)    AS lastname 
FROM   emp  

Jedynym przypadkiem, w którym możesz chcieć takiej funkcji, jest zapytanie UPDATE, które zmieni tabelę tak, aby przechowywała Imię i Nazwisko w oddzielnych polach.

Projekt bazy danych musi przestrzegać pewnych reguł, a normalizacja bazy danych jest jedną z najważniejszych

Miałem kolumnę, w której imię i nazwisko znajdowały się w jednej kolumnie. Imię i nazwisko oddzielone przecinkiem. Poniższy kod zadziałał. NIE ma sprawdzania / korygowania błędów. Tylko głupi rozłam. Użył phpMyAdmin do wykonania instrukcji SQL.

UPDATE tblAuthorList SET AuthorFirst = SUBSTRING_INDEX(AuthorLast,',',-1) , AuthorLast = SUBSTRING_INDEX(AuthorLast,',',1);

13.2.10 Składnia UPDATE

To pobiera smhg stąd i curt's z ostatniego indeksu danego podciągu w MySQL i łączy je. To jest dla mysql, wszystko, czego potrzebowałem, to uzyskać porządny podział imienia na imię i nazwisko nazwisko z nazwiskiem jedno słowo, imię wszystko przed tym pojedynczym słowem, gdzie nazwa mogłaby być pusta, 1 słowo, 2 słowa lub więcej niż 2 słowa. Tj .: Null; Mary; Mary Smith; Mary A. Smith; Mary Sue Ellen Smith;

Więc jeśli nazwa to jedno słowo lub null, last_name ma wartość null. Jeśli imie jest> 1 słowo, last_name to ostatnie słowo, a first_name to wszystkie słowa przed ostatnim słowem.

Zauważ, że już usunąłem takie rzeczy jak Joe Smith Jr.; Joe Smith Esq. i tak dalej, ręcznie, co było oczywiście bolesne, ale było wystarczająco małe, aby to zrobić, więc przed podjęciem decyzji, której metody użyć, warto sprawdzić dane w polu nazwy.

Zauważ, że to również przycina wynik, więc nie kończysz ze spacjami przed lub po nazwach.

Po prostu publikuję to dla innych, którzy mogą tu znaleźć w Google, szukając tego, czego potrzebowałem. To oczywiście działa, najpierw przetestuj go z zaznaczeniem.

To jednorazowa sprawa, więc nie obchodzi mnie wydajność.

SELECT TRIM( 
    IF(
        LOCATE(' ', `name`) > 0,
        LEFT(`name`, LENGTH(`name`) - LOCATE(' ', REVERSE(`name`))),
        `name`
    ) 
) AS first_name,
TRIM( 
    IF(
        LOCATE(' ', `name`) > 0,
        SUBSTRING_INDEX(`name`, ' ', -1) ,
        NULL
    ) 
) AS last_name
FROM `users`;


UPDATE `users` SET
`first_name` = TRIM( 
    IF(
        LOCATE(' ', `name`) > 0,
        LEFT(`name`, LENGTH(`name`) - LOCATE(' ', REVERSE(`name`))),
        `name`
    ) 
),
`last_name` = TRIM( 
    IF(
        LOCATE(' ', `name`) > 0,
        SUBSTRING_INDEX(`name`, ' ', -1) ,
        NULL
    ) 
);

Metoda, której użyłem do podzielenia first_name na first_name i last_name, gdy dane dotarły do ​​pola first_name. Spowoduje to umieszczenie tylko ostatniego słowa w polu nazwiska, więc „john phillips sousa” będzie oznaczać „john phillips” imię i „sousa” nazwisko. Pozwala to również uniknąć nadpisywania rekordów, które zostały już naprawione.

set last_name=trim(SUBSTRING_INDEX(first_name, ' ', -1)), first_name=trim(SUBSTRING(first_name,1,length(first_name) - length(SUBSTRING_INDEX(first_name, ' ', -1)))) where list_id='$List_ID' and length(first_name)>0 and length(trim(last_name))=0

UPDATE `salary_generation_tbl` SET
    `modified_by` = IF(
        LOCATE('$', `other_salary_string`) > 0,
        SUBSTRING(`other_salary_string`, 1, LOCATE('$', `other_salary_string`) - 1),
        `other_salary_string`
    ),
    `other_salary` = IF(
        LOCATE('$', `other_salary_string`) > 0,
        SUBSTRING(`other_salary_string`, LOCATE('$', `other_salary_string`) + 1),
        NULL
    );

mysql 5.4 zapewnia natywną funkcję podziału:

SPLIT_STR(<column>, '<delimiter>', <index>)