0

I am trying to split a column with over 4 millions rows into 4 new columns, The problem is i haven't a clue where to look or which term I should Google search. (Yes i have searched Google and Stack for a similar question but only found one on stack that might be a good fix but not sure if it would help, Added link further down to the question in question.)

Here is my DB.

ID  Security
1   1-5-4-6
2   2-4-06-5
3   1-4-1-2
4   1-4-1-3
5   1-45-5-32

What i am trying to do is the following, The "security" Column need to be split in 4 separate columns (group,lvl,key,code)

ID  group   lvl key code
1   1   5   4   6
2   2   4   6   5
3   1   4   1   2
4   1   4   1   3
5   1   45  5   32

The problem i am having is finding a suitable solution to split these numbers, one issue is the current security column can have up to 3 characters per - separation.

i could have a row that has 01-45-822-01 or as simply as 1-2-3-4.

One question i did find on stackoverflow
MySQL : how to split text and number with "-"

It does seem to point me in the right direction but still unsure if this would be suitable for such a large data set. over 5.9gb indexed or if there is a way to do this quicker.

Community
  • 1
  • 1
renfley
  • 146
  • 1
  • 8

1 Answers1

3

You can use SUBSTRING_INDEX to do this all in one query:

UPDATE `table`
SET
  `group` = SUBSTRING_INDEX(`Security`, '-', 1),
  `lvl` = SUBSTRING_INDEX(SUBSTRING_INDEX(`Security`, '-', 2), '-', -1),
  `key` = SUBSTRING_INDEX(SUBSTRING_INDEX(`Security`, '-', 3), '-', -1),
  `code` = SUBSTRING_INDEX(`Security`, '-', -1);

And around each of those you'll probably want to wrap a CAST(expr AS UNSIGNED) assuming you're no longer storing numbers as text.

Sammitch
  • 30,782
  • 7
  • 50
  • 77