Professional Web Applications Themes

How do I p a comma separated field? - MySQL

I have a table with the following fields: tableid (int) userid (varchar) lvalues (varchar) the lvalues column contains comma separted values. e.g. 12, 5, 32 each individual values matches a field in another table. How do I write a SQL query that ps the comma separated data? For example: WHERE user id=30 so that the results look like: 30 | 12 30 | 5 30 |32 TIA...

  1. #1

    Default How do I p a comma separated field?

    I have a table with the following fields:
    tableid (int)
    userid (varchar)
    lvalues (varchar)

    the lvalues column contains comma separted values. e.g. 12, 5, 32
    each individual values matches a field in another table.

    How do I write a SQL query that ps the comma separated data? For
    example:
    WHERE user id=30 so that the results look like:

    30 | 12
    30 | 5
    30 |32

    TIA


    John Guest

  2. #2

    Default Re: How do I p a comma separated field?

    On 14 Jun, 15:03, "John Rappold" <org> wrote: 

    You don't you build a table structure where each value has its own
    record in another table.

    From: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html
    "The first normal form (or 1NF) requires that the values in each
    column of a table are atomic. By atomic we mean that there are no sets
    of values within a column."

    Captain Guest

  3. #3

    Default Re: How do I p a comma separated field?

    This is a lookup table and the data is already there in the comma separated
    format. I can't change it.

    I'm thinking I need to do some sort of SELECT IN and then create a temporary
    table.

    Can anyone help me?
    "Captain Paralytic" <com> wrote in message
    news:googlegroups.com... 
    >
    > You don't you build a table structure where each value has its own
    > record in another table.
    >
    > From:
    > http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html
    > "The first normal form (or 1NF) requires that the values in each
    > column of a table are atomic. By atomic we mean that there are no sets
    > of values within a column."
    >[/ref]


    John Guest

  4. #4

    Default Re: How do I p a comma separated field?

    == Quote from John Rappold (org)'s article 
    > >
    > > You don't you build a table structure where each value has its own
    > > record in another table.
    > >
    > > From:
    > > http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html
    > > "The first normal form (or 1NF) requires that the values in each
    > > column of a table are atomic. By atomic we mean that there are no sets
    > > of values within a column."
    > >[/ref][/ref]

    you can use a function called substring_index to separate the values.
    --
    POST BY: lark with PHP News Reader
    lark Guest

  5. #5

    Default Re: How do I p a comma separated field?

    On Jun 14, 1:41 pm, lark <net> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    >
    > you can use a function called substring_index to separate the values.
    > --
    > POST BY: lark with PHP News Reader[/ref]

    Please check this link - I think it will do what you want:

    http://forge.mysql.com/snippets/view.php?id=4

    anokun7 Guest

  6. Moderated Post

    Default Re: How do I p a comma separated field?

    Removed by Administrator
    Jerry Guest
    Moderated Post

  7. #7

    Default Re: How do I p a comma separated field?

    anokun7 wrote:
    On Jun 14, 1:41 pm, lark <net> wrote:
    == Quote from John Rappold (org)'s article
    This is a lookup table and the data is already there in the comma separated
    format. I can't change it.
    I'm thinking I need to do some sort of SELECT IN and then create a temporary
    able.
    Can anyone help me?

    "Captain Paralytic" <com> wrote in message
    news:googlegroups.com...
    On 14 Jun, 15:03, "John Rappold" <org> wrote:
    I have a table with the following fields:
    tableid (int)
    userid (varchar)
    lvalues (varchar)
    the lvalues column contains comma separted values. e.g. 12, 5, 32
    each individual values matches a field in another table.
    How do I write a SQL query that ps the comma separated data? For
    example:
    WHERE user id=30 so that the results look like:
    30 | 12
    30 | 5
    30 |32
    TIA
     
     

    This is how I personally handle that issue:
    You can use the LIKE operator to select the comma-delimited values.
    Assuming the values are all known, turning them into a new table
    shouldn't be too difficult.

    CREATE TABLE lv12
    SELECT columns FROM table1
    WHERE uid='30' AND lvalues LIKE '%12%';

    CREATE TABLE lv5
    SELECT columns FROM table
    WHERE uid='30' AND lvalues LIKE '%5%';

    CREATE TABLE lv32
    SELECT columns FROM table
    WHERE uid='30' AND lvalues LIKE '%32%';

    CREATE TABLE newlayout
    SELECT lv12.uid AS uid,
    lv12.lvalues AS l12,
    lv5.lvalues AS l5,
    lv32.lvalues AS l32

    FROM lv12
    LEFT JOIN lv5
    ON lv12.uid = lv5.uid
    LEFT JOIN lv32
    ON lv12.uid = lv32.uid;

    There might be a more elegant way to actually seperate the columns, but
    the method here will work.

    --
    Temi Solo Dio

    Brian M Napoletano
    net
    http://www.napoletano.net
    Brian Guest

Similar Threads

  1. Importing a Comma Separated File
    By Nemex2 in forum Coldfusion - Advanced Techniques
    Replies: 2
    Last Post: October 1st, 04:49 PM
  2. Getting the Columns comma separated
    By Babu in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 16th, 02:23 PM
  3. Replies: 0
    Last Post: July 15th, 06:44 PM
  4. comma separated to SQL Server DB table
    By Aaron Bertrand [MVP] in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 7th, 07:24 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139