CODEWITHSUNDEEP
mysql
Chan
Chan

Reputation: 1959

Can I get string position in other column

If I have two columns and data like:

column a | column b

   1    | 1,2,3,4,5
   4    | 1,2,3,4,5
   5    | 1,2,3,4,5

Is there any function to get the column a position in column b, the first data result is 0, the second is 3, and the third should be 4

Upvotes: 2

Views: 32

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175606

You could use FIND_IN_SET:

SELECT col_a, colb_b,
       FIND_IN_SET(col_a, col_b) - 1 AS result
FROM tab;

SqlFiddleDemo

Output:

╔═══════╦════════════╦════════╗
║ cola  ║   colb     ║ result ║
╠═══════╬════════════╬════════╣
║    1  ║ 1,2,3,4,5  ║      0 ║
║    4  ║ 1,2,3,4,5  ║      3 ║
║    5  ║ 1,2,3,4,5  ║      4 ║
╚═══════╩════════════╩════════╝

If not found you will get -1.

In SQL functions are mostly 1-based. That's why you need -1.

I strongly suggest to normalize your data, and store atomic values in column.

Upvotes: 2

Related Questions