MySQL: Split Column into Two

MySQL: Split column into two

1. use alter table - add new column.
2. update number column + Country.Number : use substring_index

Query:

UPDATE TABLE SET Number = SUBSTRING_INDEX('Country.Number', '.', -1),
Country.Number = SUBSTRING_INDEX('Country.Number', '.', 1);

3. alter table change field name of Country.Number

Split MYSQL column into multiple columns

Assume your data is actually looking like following:

addr
=======================
street, City, State ZIP

And here is the SQL:

SELECT addr,
  substr(addr, 1, length(addr) - length(substring_index(addr, ',', -2))) street,
  substring_index(substring_index(addr, ',', -2), ',', 1) city,
  substr(trim(substring_index(addr, ',', -1)),1,2) state,
  substring_index(addr, ' ', -1) zip
FROM tab

OOPs there is an extra comma at street, this is a homework for you to fix :)

MySQL - Split two columns into two different rows

You can use UNION ALL and an ORDER BY Totalwatch DESC to get the results ordered according to Totalwatch.

SELECT HomeTeam AS Teams, Totalwatch  FROM YourTable
UNION ALL
SELECT AwayTeam, Totalwatch FROM YourTable
ORDER BY Totalwatch DESC;

Split value from one field to two

Unfortunately MySQL does not feature a split string function. However you can create a user defined function for this, such as the one described in the following article:

• MySQL Split String Function by Federico Cargnelutti

With that function:

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 ;

you would be able to build your query as follows:

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

If you prefer not to use a user defined function and you do not mind the query to be a bit more verbose, you can also do the following:

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

SQL - Split a column into two columns in Mysql

You didn't post the database engine, so I'll assume PostgreSQL where the modulus operand is %.

The query should be:

select o.letter, e.letter
  from (
    select id, letter, id as base from my_table where id % 2 = 0
  ) o full outer join (
    select id, letter, (id - 1) as base from my_table where id % 2 <> 0
  ) e on e.base = o.base
  order by coalesce(o.base, e.base)

Please take the following option with a grain of salt since I don't have a way of testing it in MySQL 5.6. In the absence of a full outer join, you can perform two outer joins, and then you can union them, as in:

select * from (
  select o.base, o.letter, e.letter
    from (
      select id, letter, id as base from my_table where id % 2 = 0
    ) o left join (
      select id, letter, (id - 1) as base from my_table where id % 2 <> 0
    ) e on e.base = o.base
  union
  select e.base, o.letter, e.letter
    from (
      select id, letter, id as base from my_table where id % 2 = 0
    ) o right join (
      select id, letter, (id - 1) as base from my_table where id % 2 <> 0
    ) e on e.base = o.base
) x
order by base

how to split a column into multiple columns in mysql

With the given example data you can do something as below -

mysql> create table test (marks varchar(100));
Query OK, 0 rows affected (0.12 sec)

mysql> insert into test values ('Maths-80,Phy-100,Che-99'),('Maths-90,Phy-60'),('Phy-82,Che-65'),('Che-90'),('Maths-33,Phy-89,Che-65'),('Maths-50,Phy-43,Che-59');
Query OK, 6 rows affected (0.04 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from test ;
+-------------------------+
| marks                   |
+-------------------------+
| Maths-80,Phy-100,Che-99 |
| Maths-90,Phy-60         |
| Phy-82,Che-65           |
| Che-90                  |
| Maths-33,Phy-89,Che-65  |
| Maths-50,Phy-43,Che-59  |
+-------------------------+
6 rows in set (0.00 sec)

Now using locate and substring_index we can extract the values as

select marks, 
case 
 when locate('Maths',marks) > 0 then substring_index(substring_index(marks,'Maths-',-1),',',1) else 0 end 
as Maths ,
case 
 when locate('Phy',marks) > 0 then substring_index(substring_index(marks,'Phy-',-1),',',1) else 0 end 
as Phy ,
case 
 when locate('Che',marks) > 0 then substring_index(substring_index(marks,'Che-',-1),',',1) else 0 end 
as Che 
from test ;

Output :

+-------------------------+-------+------+------+
| marks                   | Maths | Phy  | Che  |
+-------------------------+-------+------+------+
| Maths-80,Phy-100,Che-99 | 80    | 100  | 99   |
| Maths-90,Phy-60         | 90    | 60   | 0    |
| Phy-82,Che-65           | 0     | 82   | 65   |
| Che-90                  | 0     | 0    | 90   |
| Maths-33,Phy-89,Che-65  | 33    | 89   | 65   |
| Maths-50,Phy-43,Che-59  | 50    | 43   | 59   |
+-------------------------+-------+------+------+
6 rows in set (0.00 sec)

Split single text column into two columns of all possible combinations

Change your join condition to use an < inequality:

SELECT
    c1.cust_id,
    c1.cust_email,
    c2.cust_email
FROM cust_info c1
INNER JOIN cust_info c2
    ON c1.cust_id = c2.cust_id AND
       c1.cust_email < c2.cust_email;

This should prevent the sort of duplicate pairs you are currently seeing.

---

Related Topics