CODEWITHSUNDEEP
mysqlstored-proceduressplitrecordstored-functions
Max
Max

Reputation: 5063

in MySQL, How to split a string value and insert each token in a table?

I have a table named "keywords" with the following fields:

ID [primary key] | keyword [varchar(255)]

keyword field of each record contains a string values like "this, is, only, a test"

I have no idea how to create a stored procedure to split this string with "," as delimiter and insert each token as a new record inside a new table.

thank You in advance.

Upvotes: 0

Views: 1491

Answers (3)

EscalinNancy
EscalinNancy

Reputation: 1272

  1. Using instr() find the first occurance of the delimiter(,).

  2. Substring() to split input string from zeroth char till first occurance-1 character Now you have separated the first string. Insert separated string into table.

  3. Use replace() to the input string to remove the first value.

  4. Repeat steps 1 till 3, until your input string is empty. Hope this works.. Good luck..

Upvotes: 0

abon999
abon999

Reputation: 336

here, difficulty is to cut your string with a delimiter. You need to declare a FUNCTION like this : http://fdegrelle.over-blog.com/article-1342263.html after, you just need to store values in tables, it's easy. Good luck!

Upvotes: 1

King Skippus
King Skippus

Reputation: 3826

MySQL doesn't have any kind of SPLIT() or EXPLODE() string function. In a nutshell, you need to manually iterate over the string searching for commas and insert the value when you reach a delimiter (comma) or end of the string. Alternatively you could try doing fancy gyrations with INSTR() and such, your mileage may vary.

I would suggest reading some of the comments at the bottom of the String Functions reference page at MySQL. Do a search-on-page (Ctrl-F) for split and you'll see some useful ideas.

Upvotes: 0

Related Questions