Display values until the first space in MYSQL and PHP

Question

I'm trying to display values until the first space in MYSQL and PHP. For example,

1 items from 29
100 items from 10

In the first example I want to display only the number 1, and in the second example I want to display only the number 100 and so on. Here is my code,

$ordering = "SELECT t.id, t.cart_id, t.full_name, t.description, t.txn_date, t.grand_total, c.paid, c.shipped
            FROM transactions t
            LEFT JOIN cart c ON t.cart_id = c.id
            WHERE c.paid = 1 AND c.shipped = 0
            ORDER BY t.txn_date

Now, I want to replace t.description with the code of that displays until the first space. I've searched in the internet and found the code:

SELECT REVERSE(RIGHT(REVERSE(YourColumn), LEN(YourColumn) - CHARINDEX(' ', REVERSE(YourColumn))))

So, I replaced t.description with this:

$ordering = "SELECT t.id, t.cart_id, t.full_name, t.REVERSE(RIGHT(REVERSE(description), LEN(description) - CHARINDEX(' ', REVERSE(description)))), t.txn_date, t.grand_total, c.paid, c.shipped
                FROM transactions t
                LEFT JOIN cart c ON t.cart_id = c.id
                WHERE c.paid = 1 AND c.shipped = 0
                ORDER BY t.txn_date

Is that right? And what is the best way to it?

Can you explain your requirement in detail? "first space in MYSQL" what does that mean? — , Apr 08 '19 at 06:12
Also you can do this in php like `$array = explode(' ', $description,2);` and you will get 100 for `$array[0]` — Rasa Mohamed, Apr 08 '19 at 06:20

score 1 · Answer 1 · answered Apr 08 '19 at 06:10

With MySQL, the easiest way to do this is with SUBSTRING_INDEX e.g.

SELECT SUBSTRING_INDEX('100 items from 10', ' ', 1)

With a positive third parameter (count, in this case 1), it returns everything to the left of the count occurrence of the second parameter (in this case space). So this call returns everything to the left of the first space:

Demo on dbfiddle

In your query you would write

$ordering = "SELECT t.id, t.cart_id, t.full_name, SUBSTRING_INDEX(t.description, ' ', 1) AS description, t.txn_date, t.grand_total, c.paid, c.shipped
            FROM transactions t
            LEFT JOIN cart c ON t.cart_id = c.id
            WHERE c.paid = 1 AND c.shipped = 0
            ORDER BY t.txn_date

Display values until the first space in MYSQL and PHP

1 Answers1