Splitting up a name in SQL

Question

How can I split up a name before/after the and insert into a new table with other attributes that both artists have?
I prefer to split and keep in same table.My Artists table contains Artist, PageURL, AlbumURL.

My table results:

Please take a loot at MySql documentation describing string functions https://dev.mysql.com/doc/refman/8.0/en/string-functions.html. I think SUBSTRING_INDEX function should work for you. — Kishore Kirdat, Jul 23 '19 at 04:22
I already tried that with `code`SELECT SUBSTRING_INDEX(Artist, 'and' , 2) from Artists`code` and I don't get the results — J S, Jul 23 '19 at 04:26

score 1 · Answer 1 · edited Jul 23 '19 at 07:21

You can use the following split up query to get name seperated :

  SELECT SUBSTRING_INDEX(Artist,' And ',1) as name1, 
         SUBSTRING_INDEX(Artist,' And ',-1) as name2 ,PageURL,AlbumURL 
  From Artists

Add this select statement into an insert query for the new table

    INSERT INTO Artists2
    SELECT SUBSTRING_INDEX(Artist,' And ',1) as name1, 
         SUBSTRING_INDEX(Artist,' And ',-1) as name2 ,PageURL,AlbumURL 
    From Artists

Khairul Alam · Answer 2 · 2019-07-23T04:40:46.067

0

This may help...

DECLARE @Vlaue VARCHAR(100) = 'ABCD and EFGH'

DECLARE @index INT = (SELECT CHARINDEX('and', @Vlaue))
SELECT SUBSTRING(@Vlaue,1,@index-1) as FirstPart, SUBSTRING(@Vlaue,@index+3,LEN(@Vlaue) - @index) SecondPart
--SELECT SUBSTRING(@Vlaue,1,CHARINDEX('and', @Vlaue)-1) as FirstPart, SUBSTRING(@Vlaue,CHARINDEX('and', @Vlaue)+3,LEN(@Vlaue) - @index) SecondPart

edited Jul 23 '19 at 04:40

answered Jul 23 '19 at 04:27

Khairul Alam

1,266
2
11
31

DatabaseCoder · Answer 3 · 2019-07-23T05:41:28.693

0

Below query will split two names but if you have more than two names in Artist column then you have to create MySQL function.

select
    *,
    substring_index(Artist, ' And ', 1) as FirstArtist,
    substring_index(Artist, ' And ', -1)  as LastArtist
from Artists;

Check this post- Split value from one field to two

Do you want below stuff? please confirm.

Assume current data -

Artist
Metz And Trix
Djo2 And DJ Srk

Required data in same table -

Artist
Metz
Trix
Djo2
DJ Srk

Solution- You need to insert second artists then update the first artists records. Please execute insert command first then update command. Please test before applying to production.

insert into Artists (Artist, PageURL, AlbumURL)
select substring_index(Artist, ' And ', -1), PageURL, AlbumURL
from tmp.Artists;

update Artists
set Artist = substring_index(Artist, ' And ', 1)
where Artist like '% And %'

edited Jul 23 '19 at 05:41

answered Jul 23 '19 at 04:27

DatabaseCoder

2,004
2
12
22

I do have two artists in the same column with and in between them. – J S Jul 23 '19 at 04:29
I got this to work with code `code`SELECT SPLIT_STR(Artist, ' And ', 1) as Artist1, SPLIT_STR(Artist, ' And ', 2) as Artist2 FROM Artists WHERE Artist Like '% And %' `code`. Can you please tell me how can I insert the same attributes that I already have and insert both Artists into Artists table? – J S Jul 23 '19 at 04:42
If you already have data in your table and you want to split and save data into other columns then you need `update` command instead of `insert` command. please provide your table column structure. – DatabaseCoder Jul 23 '19 at 04:55
I would do update but the only problem is I want to make another row for Artist2 the one after the and and insert into Artists Table. I only know how to do update if there is an error in the name but can you show me how to split up string and copy attributes? – J S Jul 23 '19 at 05:07
I have edited my answer. Please confirm that I am assuming in correct way. – DatabaseCoder Jul 23 '19 at 05:17
yes, that is what I want. And the other thing is I want to copy the attributes of Metz And Trix which are PageURL, AlbumURL to Metz , PageURL, AlbumURL, and Trix PageURL, AlbumURL – J S Jul 23 '19 at 05:19
Added solution to answer. – DatabaseCoder Jul 23 '19 at 05:41

Splitting up a name in SQL

3 Answers3