0

I've a MySQL table with the following data.

My Table

When I pass the login_id, value should display like rows and it should be select query only and should not any other methods.

Example: when I pass login_id as "22", it should display like below:

Need like below

halfer
  • 19,824
  • 17
  • 99
  • 186
Phoenix
  • 1,470
  • 17
  • 23
  • 1
    you should not use comma separated value .. you should normalize the data .. – ScaisEdge Aug 13 '19 at 05:31
  • Does each employee always have 2, and only 2, applicable branches? Or, could there be an employee with fewer or greater than 2? – Tim Biegeleisen Aug 13 '19 at 05:31
  • possible duplicate - https://stackoverflow.com/a/17942691/2845389 – Kaushik Aug 13 '19 at 05:32
  • 1
    @Kaushik : this is not duplicate. link mentioned by you is limited values only it will picks. – Phoenix Aug 13 '19 at 05:34
  • @TimBiegeleisen : Here i've give sample entry only, like this each employee have more than one applicable branches. Example : 1 have 10 branches, once have 25 branches – Phoenix Aug 13 '19 at 05:36
  • You can't easily handle this requirement from MySQL, not without something like a stored procedure. The best practice here is to _not_ even bring such CSV data into your database in the first place. – Tim Biegeleisen Aug 13 '19 at 05:37
  • @TimBiegeleisen : i'm just learning, is there any possibilities to do so in Mysql query without using store procedures. – Phoenix Aug 13 '19 at 05:39
  • 1
    If you're just learning, and this table is not running in a production system, then you should absolutely take this opportunity to fix your table design, and get rid of those CSV data. Instead, start off with one row for each employee and branch. Better yet, create a junction table to store these 1-to-1 relationships, and keep the metadata in two other tables. – Tim Biegeleisen Aug 13 '19 at 05:40

2 Answers2

0

Try this

SELECT SUBSTRING_INDEX(`applicable_branches`, ', ', 1) AS`speditor1`, SUBSTRING_INDEX(`applicable_branches`, ', ', -1) AS `speditor2` FROM `myTable` where `id`= 22

You can read more about it at https://coderwall.com/p/zzgo-w/splitting-strings-with-mysql

Stefan Avramovic
  • 1,365
  • 2
  • 11
  • 20
0

MySQL doesn't provide any built-in function for doing this. Although you can manage doing it by several ways from which one of the way is.

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(applicable_branches, ',', number), ',', -1) output FROM table_name, (SELECT 1 number UNION ALL
               SELECT 2 UNION ALL
               SELECT 3) numbers;

Where number can be the maximum occurrences of comma that you think can occur in the column value.

Here the max number will be 3.

Add SELECT n UNION ALL if needed for occurrences.

monishvadhwani
  • 71
  • 2