Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Sep 2012
    Posts
    76
    Thanks
    61
    Thanked 0 Times in 0 Posts

    Splitting a string in a field?

    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

    Code:
    :12AB:
    Or this

    Code:
    :RA1F:52FA:

    Or this

    Code:
    :RA1F:52FA:3FG1:
    Or this

    Code:
    :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
    Last edited by Oatley; 12-14-2012 at 11:49 AM. Reason: Changed edit

  • #2
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,641
    Thanks
    0
    Thanked 649 Times in 639 Posts
    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.
    Last edited by felgall; 12-12-2012 at 06:29 PM.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • Users who have thanked felgall for this post:

    Oatley (12-14-2012)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,567
    Thanks
    78
    Thanked 4,385 Times in 4,350 Posts
    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
    Code:
    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:
    Code:
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Oatley (12-14-2012)

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,567
    Thanks
    78
    Thanked 4,385 Times in 4,350 Posts
    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:
    Code:
    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
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Oatley (12-14-2012)

  • #5
    New Coder
    Join Date
    Sep 2012
    Posts
    76
    Thanks
    61
    Thanked 0 Times in 0 Posts
    Thanks all, you are correct I've managed to normalize the table and works great now. Thank you for your help.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •