Now i want count no of items in one particular row in one particular cell.
i.e If i have 25,45,26,45,46 in a cell in table.
I want the output like 5
Solution:
If data is end with delimiter then
Query: SELECT LENGTH(yourColumn) - LENGTH(REPLACE(yourColumn, ',', '')) AS numberOfItemsInRow FROM yourTable;
example:
SELECT LENGTH('123,123,') - LENGTH(REPLACE('123,123,', ',', '')) AS numberOfItemsInRow;
o/p: 2
If data is not end with delimiter
Query: SELECT LENGTH(yourColumn) - LENGTH(REPLACE(yourColumn, ',', '')) + 1 AS numberOfItemsInRow FROM yourTable;
example:
SELECT LENGTH('123,123') - LENGTH(REPLACE('123,123', ',', ''))+1 AS numberOfItemsInRow;
o/p: 2