CODEWITHSUNDEEP
mysqlstringreturn-valueextract
Nowitz41
Nowitz41

Reputation: 271

Locate text position, extract text and insert in new column in MySQL

I have the following example of rows in a MySQl table

              Column A    

Row1    Lauguage=English&Country=USA&Gender=Male
Row2    Gender=Female&Language=French&Country=
Row3    Country=Canada&Gender=&Language=English

How can I achieve the following: For example, I need to look for Country

  1. I need to locate the position of Country in this text column. This changes from row to row.
  2. I need to then ignore the parameter 'Country=' and only extract the value. In some cases this will be NULL (like example in Row2) while in some rows I need the values followed by '=' (like example in Row1 & Row3). But, I need to make sure I get the value only. Not the next parameter separated by '&'
  3. Once I have extracted the values of the Country parameter, I need to create a new column where these values will now be extracted and stored.

End Result: New Column

              Column B                                                

Row1            USA                            
Row2                              
Row3           Canada

Any help here would be appreciated! Thanks!

Upvotes: 12

Views: 15481

Answers (2)

mRyan
mRyan

Reputation: 390

I had the exact same question however, in SQL for anyone wanting the SQL answer see Tim Biegeleisen comment here 

create table elb_logs(row varchar(100), url varchar(100));
insert into elb_logs values("Row1", "Lauguage=English&Country=USA&Gender=Male");

SELECT
    row,
    CASE WHEN POSITION('Country=' IN url) > 0
         THEN SPLIT_PART(SPLIT_PART(url, 'Country=', 2), '&', 1)
         ELSE 'Missing Country!' END AS ColumnB
FROM elb_logs;

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562621

You can pick the text following the 'Country=', and then once you have that substring, pick the text before the first '&'

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(ColumnA, 'Country=', -1), '&', 1) AS ColumnB
FROM `atable`

See http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_substring-index

Here's a test to demonstrate:

mysql> SELECT * FROM atable;
+------+------------------------------------------+
| row  | columna                                  |
+------+------------------------------------------+
| Row1 | Lauguage=English&Country=USA&Gender=Male |
| Row2 | Gender=Female&Language=French&Country=   |
| Row3 | Country=Canada&Gender=&Language=English  |
+------+------------------------------------------+

mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(ColumnA, 'Country=', -1), '&', 1) AS ColumnB FROM atable;
+---------+
| ColumnB |
+---------+
| USA     |
|         |
| Canada  |
+---------+

Re your followup question:

INSERT INTO atable VALUES ('Row4', 'Gender=&Language=English');

SELECT `row`, IF(LOCATE('Country=', ColumnA)>0, 
  COALESCE(
    NULLIF(SUBSTRING_INDEX(SUBSTRING_INDEX(ColumnA, 'Country=', -1), '&', 1), ''), 
    'Blank string is not valid!'), 
 'Missing Country!') AS ColumnB     
FROM `atable`

+------+----------------------------+
| row  | ColumnB                    |
+------+----------------------------+
| Row1 | USA                        |
| Row2 | Blank string is not valid! |
| Row3 | Canada                     |
| Row4 | Missing Country!           |
+------+----------------------------+

Upvotes: 20

Related Questions