0

i have a table name rankList with columns instituteCode and rank

Table rankList data

----------------------------------------------------
instituteCode       |  rank                     
----------------------------------------------------
1125                |  1,3,7
1259                |  11,16,19,28
1902                |  2,4,5,6,8,9,10
----------------------------------------------------

so as per the example table we can see all the rank are stored in single column, so i need a desired output to show the selected instituteCode ranks must split in different column

so i already tried this using regular expression for the instituteCode 1125

select 
SUBSTRING_INDEX(rank,',',1) as  firstColumn,
SUBSTRING_INDEX(SUBSTRING_INDEX(rank,',',2),',',-1) as secondColumn,
SUBSTRING_INDEX(rank,',',-1) as  thirdColumn
from ranklist where instituteCode='1125'

Output is 

------------------------------------------
firstColumn | secondColumn | thirdColumn
------------------------------------------
1           | 3            |7
------------------------------------------

but the problem is, to get the output for 1259 and 1902 i must rewrite the entire query. is there any other way to split according to the rank and store in multiple columns.

Intact Abode
  • 382
  • 5
  • 20
  • Possible duplicate of [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – Madhur Bhaiya Nov 12 '18 at 06:21
  • rank are unique for individual instituteCode, wont it help ? – Intact Abode Nov 12 '18 at 06:32
  • You need to fix the original data (and how do you store it). Normalize it. Otherwise, you will get into requirements for such complex queries later, which will be extremely inefficient. – Madhur Bhaiya Nov 12 '18 at 06:52
  • Let's say you have 100 comma separated values in a column. Now, you can imagine how many substring_index you would write. – Madhur Bhaiya Nov 12 '18 at 06:53

0 Answers0