CODEWITHSUNDEEP
mysqlseparator
FixFaier
FixFaier

Reputation: 237

MySQL separate and select values from one value field

I have the following:

id | value
1 | abc:432:xyz
2 | 678:123:iop

And like to get:

id | valueA | valueB | valueC
1 | abc | 432 | xyz
2 | 678 | 123 | iop

Is this possible by sql query? Thx!

Upvotes: 2

Views: 461

Answers (4)

ashutosh madheshiya
ashutosh madheshiya

Reputation: 11

use this

create table test2 as SELECT id,substr(value,1,3) as valueA,substr(value,5,3) as valueB,substr(value,9,3) as valueC FROM test

Upvotes: 0

Code Lღver
Code Lღver

Reputation: 15603

SELECT id, 
       SUBSTRING_INDEX(`value`, ':', 1) as valueA, 
       SUBSTRING(`value`,5,3) as valueB, 
       SUBSTRING(`value`,9) as valueC 
          FROM Table_name

use above query.

Upvotes: 0

Joke_Sense10
Joke_Sense10

Reputation: 5402

It's possible using sql query:

SELECT id,SUBSTRING_INDEX(`value`, ':', 1) AS valueA ,
SUBSTRING_INDEX(SUBSTRING_INDEX(value,':',2),':',-1) AS valueB ,  SUBSTRING_INDEX(SUBSTRING_INDEX(value,':',-2),':',-1) AS valueC 
FROM   tablename;

Upvotes: 2

deviloper
deviloper

Reputation: 7240

one way is to separate them after you read the query results!

Query:

$this->db->query("select id, value from tablename");

Example in PHP (Could be anything):

foreach($resultRecord as $record){
    $segment = explode(":",$record->value);
    echo "<tr>";
    echo "<td>".$record->id;
    for($i=0;$i<count($segment);i++){
       echo "<td>".$segment[i];
    }
}

Upvotes: 0

Related Questions