How to select first 3 values from a mysql concetenated column

Question

I have a column in a table called multi_values.

In that column I am inserting multiple values by using + separator.

For example

01234567891111+01234567891112+01234567891113+01234567891114

This column may contain some times two values , some times 4 values dynamically.

So I would like to get first 3 values from that column.

If that column contains only 2 values then 2 , if it contains only one value then one.

I tried with mysql substring.

But the values are not fixed length in that column.

So I need a select query to get first 3 values from that column.

Any help would be greatly appreciated.

`Answer`

At the time of inserting in that particular column values are inserting starting with + sign. Like +123+2344+34563 like this. So here is the solution.

SUBSTR(column_name,2) . So this will remove the first + sign. Below one giving what I expect SUBSTRING_INDEX(SUBSTR(column_name,2),'+',3). So it will return 3 Ids.

Hello, I don't know how this question is duplicated? This question is clearly explaining what is there and what I need. How some one marked this as duplicate? — user3408779, Mar 12 '21 at 17:52
I disagree with the closing, too. You can argue that a data model is sub-optimal, but that doesn't mean this is a duplicate. The OP has data in a certain format. Re-architecting the entire system might not be on the table right now. — Chris Haas, Mar 12 '21 at 17:53
Agreed with the closing as well, have you looked at SUBSTRING_INDEX? Specifically the example for the IP addresses - https://www.w3resource.com/mysql/string-functions/mysql-substring_index-function.php — hppycoder, Mar 12 '21 at 17:56
@hppycoder I clearly mentioned I tried with substring. But the count was not fixed length. In that case how can I make fixed length? — user3408779, Mar 12 '21 at 17:59
I'm just trying to help. I know you stated SUBSTRING was attempted but I did find this on SO which might help - https://stackoverflow.com/questions/38137363/mysql-query-to-parse-string-with-unknown-length — hppycoder, Mar 12 '21 at 18:06

IBeThieving · Answer 1 · 2021-03-12T18:23:09.807

You simply fetch the data using then split it and loop through the array.

Database class (Provided this class so you can see how I am fetching the data)

<?php

class Database
{
    //Instance of connection
    private static $_instance = null;

    //database connection variables
    private $servernane;
    private $username;
    private $password;
    private $dbname;
    private $options;
    private $dsn;

    // database operation variables
    private $_pdo;

    public function __construct()
    {
        $this->servername = "localhost";
        $this->username = "root";
        $this->password = "";
        $this->dbname = "crud";
        $this->options = [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_CASE => PDO::CASE_NATURAL,
            PDO::ATTR_ORACLE_NULLS => PDO::NULL_EMPTY_STRING,
        ];

        try 
        {
            $this->dsn = "mysql:host={$this->servername};dbname={$this->dbname}";
            $this->_pdo = new PDO($this->dsn, $this->username, $this->password);
            echo "Connected sucessfully to the database";
        } catch (PDOExeption $e) 
        {
            echo "connection failed: " . $e->getMessage();
        }
    }

    public static function getInstance()
    {
        if (!isset(self::$_instance)) {
  

      self::$_instance = new Database();
    }
    return self::$_instance;
}
public function query($sql, $params = array())
{
    $stmt = $this->_pdo->prepare($sql);
    $stmt->execute($params);

    return $stmt;
}

}

Now here is the solution.

<?php

require_once './classes/Database.class.php';

$db = Database::getInstance();

$stmt = $db->query('select multi_values from example WHERE id = :id', 
        array('id' => 1));

$data = $stmt->fetch()['multi_values'];

$data_arr = explode("+", $data);


if (count($data_arr) === 1 ) {
    echo $data_arr[0]; 
}elseif (count($data_arr) === 2 ) {
    for ($x = 0; $x <= 1; $x++) {
        echo $data_arr[$x];
    }
}elseif (count($data_arr) === 3 ) {
    for ($x = 0; $x <= 2; $x++) {
        echo $data_arr[$x];
    }
}
elseif (count($data_arr) === 4 ) {
    for ($x = 0; $x <= count($data_arr) - 2; $x++) {
        echo $data_arr[$x];
    }
}

While a good solution to the problem it doesn't match what the OP needs. They will be splitting based on the criteria for the `+` sign then most likely using that data to perform some other SQL analysis. — hppycoder, Mar 12 '21 at 17:58
I have amended the answer to do exactly what he requested my apologies. — IBeThieving, Mar 12 '21 at 18:17
@IBe Thieving , Thank you for your time. Actually i need the solution from mysql. But I figured out this one . `SELECT SUBSTRING_INDEX(SUBSTR(`multi_tracking_ids`,2),'+',3)` — user3408779, Mar 12 '21 at 18:18
Glad to see you got resolution @user3408779! SUBSTRING_INDEX combined with SUBSTR. Thanks for following up — hppycoder, Mar 12 '21 at 18:19
Ahh I see it was tagged with PHP so I assumed you wanted it done programmatically via PHP glad to see you got your answer anyway! — IBeThieving, Mar 12 '21 at 18:24

How to select first 3 values from a mysql concetenated column

1 Answers1