I have a table named Garden.
In the Garden table there is column named Fruit with thousands of rows
and each row of Fruit has comma seperated values like Apple, Banana, Pear and so on.
I want the get the count of the number fruits in each row in Fruit column
like if first row has Apple, Banana, Pear, then the count should return 3.
How to write a sql query for it.
I tried using regex but its not working.
If you are using Oracle database then you can use REGEXP_COUNT regular expression.Find the below example:-
create table garden( fruit varchar2(1000));
insert into garden VALUES('apple,bananan,pear');
insert into garden VALUES('orange,bananan');
insert into garden VALUES('apple,bananan,pear,orange');
select * from garden;
output:-
FRUIT
------------------
apple,bananan,pear
orange,bananan
apple,bananan,pear,orange
Now you can use below query to get the count.
select REGEXP_COUNT(Fruit, ',')+1 as fruit_count from garden;
This will work:
select length(Fruit) - length(replace(Fruit, ',', '')) + 1 from Garden
This part len(Fruit) - len(replace(Fruit, ',', '')) returns how many commas are in your string and there are always one item more than commas, so we need to add 1.
LENGTH. Updated my answer and it will work now.
postgresql,oracle,db2,sql-server, ...