Resolved Splitting a string in a field?

[Oatley]

Hello, I'm after some help please.

I have a field in my database called 'reference_code' for blog I'm creating.

In the field in each record I have reference codes which

1- contain a : character Between each alphanumeric value
2- There are a min number of 1 of values between each starting and ending : and a maximum of 4
3- They Are followed by a four digit alphanumeric code, So in each record the field for 'code' could look like:

This

:12AB:

Or this

:RA1F:52FA:

Or this

:RA1F:52FA:3FG1:

Or this

:RA1F:52FA:3FG1:ASD3:

In the field 'code' - and the code needs to be like this, so it's not a case of normalising my database here.

Now my question is this, is there any way I can explode on the : character and alias that one column into four just extracting each code individually? I've tried it using SUBSTRING_INDEX, but can't work it out.

Thank you

[felgall]

If the values represent a single code then why do you need to split it.

If they represent multiple codes then it is a case of normalising the data. What would you do if the code contained a million such fragments that don't fit in the one field.


Perhaps you can explain how the code works - then we might be able to suggest the best way to handle it - since if neither of the above apply is is an unusual situation.

[Old Pedant]

and the code needs to be like this, so it's not a case of normalising my database here.

I strongly disagree on both points.

No, it does *NOT* need to be like that.

Yes, it *IS* a case of needing to normalize your database.

Assuming that you now have something like

CREATE TABLE blog (
    blogid INT AUTO_INCREMENT PRIMARY KEY,
    other XXX,
    fields YYY,
    reference_code VARCHAR(xxx) 
);

You *NEED* to change the to TWO tables:

CREATE TABLE blog (
    blogid INT AUTO_INCREMENT PRIMARY KEY,
    other XXX,
    fields YYY
);
CREATE TABLE blog_references (
    blogid INT,
    reference_code VARCHAR(4),
    CONSTRAINT FOREIGN KEY (blogid) REFERENCES blog(blogid)
);

Anything else will be poor database design.

[Old Pedant]

The point being, if you ever really *NEED* to see the format ":RA1F:52FA:3FG1:ASD3:" it is much easier to generate that from the normalized form that try to create a temporary normalized form from the unnormalized stuff you have now.

For example:

SELECT B.blogid, B.other, B.fields, CONCAT(':', GROUP_CONCAT(R.reference_code SEPARATOR ':'),':') AS codes
FROM blog AS B
LEFT JOIN blog_reference AS R ON B.blogid = R.blogid
GROUP BY B.blogid, B.other, B.fields

[Oatley]

Thanks all, you are correct I've managed to normalize the table and works great now. Thank you for your help.