CODEWITHSUNDEEP
mysqllaravel-5
Thilagam
Thilagam

Reputation: 65

Get the column value by considering multiple delimiters in MySQL and Laravel 5.2

Invoice No

12345/1

789_2

The output i require is as follows

Invoice No

12345

789

Kindly help me to achieve the above result using mysql and laravel 5.2.

Thanks in advance

Upvotes: 1

Views: 257

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521914

In MySQL, we can use SUBSTRING_INDEX to conditionally take the substring of the invoice depending on the presence/absence of separator characters:

SELECT
    CASE WHEN invoice_no LIKE '%/%' THEN SUBSTRING_INDEX(invoice_no, '/', 1)
         WHEN invoice_no LIKE '%\\\\%' THEN SUBSTRING_INDEX(invoice_no, '\\', 1)
         WHEN invoice_no LIKE '%\_%' THEN SUBSTRING_INDEX(invoice_no, '_', 1)
         ELSE invoice_no END AS invoice_no_new
FROM yourTable;

enter image description here

Demo

Upvotes: 1

Related Questions