How can I get and separate the result from one column to be inserted on a new table

Question

I have a column let's say note and the values inside the notes is like:

Medicine. This needs to be use with the right prescription. wwww.test.comURL:www.test.com/medicine/test.php

I want to extract or get the result of those in 3 parts:

1. Medicine
2. This needs to be use with the right prescription.
3. www.test.com/medicine/test.php

But, how can I do that using a select statement in MySQL? Any help is highly and really appreciated!

Answer 1

Well, the simple answer is: Not easily. You can write a function which will do this for you.

You should read the following solution: Split value from one field to two

Taken from the above answer:

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 improve your question, I can give you a more specific answer on how to implement this, but you have provided little information about the structure of this column or how it changes from row to row.

EDIT:

A bit of the usage of this function: It takes in 3 parameters.

1. The string you want to split up.

2. A delimiter. That is, one or more character(s) you want to split your string on.

3. An index. Use this to select which part of the split up string you want.

So for example if your column was formatted as such:

SELECT summary
FROM MyTable

Result:

          summary
----------------------------
 "foo. bar. www.foobar.com"

You could put something like:

SELECT SPLIT_STR(summary, '. ', 1) AS a_summary,
       SPLIT_STR(summary, '. ', 2) AS a_description,
       SPLIT_STR(summary, '. ', 3) AS a_url,
FROM MyTable

An output might look something like:

a_summary | a_description |    a_url
------------------------------------------
   foo    |     bar       | www.foobar.com