CODEWITHSUNDEEP
sqlsql-serverdatabasesplit
user2443401
user2443401

Reputation: 77

MS SQL - Count the occurrences of a word in a field

I'm having a column in a table which contains data as shown below

I need to find the occurences and the name of the fruit and return the output in two columns

Oranges - Oranges(2)
Apple - Apple(2)
Mango - Mango(1)

Is it possible to combine LIKE and COUNT function to get the desired results.

Upvotes: 1

Views: 1548

Answers (2)

Nitika
Nitika

Reputation: 463

you have to create a function same as below.

CREATE FUNCTION [dbo].[split](
    @delimited NVARCHAR(MAX),
    @delimiter NVARCHAR(100)
) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
    DECLARE @xml XML
    SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
    INSERT INTO @t(val)
    SELECT  r.value('.','varchar(MAX)') as item
    FROM  @xml.nodes('/t') as records(r)
    RETURN
END

then Eg.

create table #Fruits (
  id int identity(1,1),
  string nvarchar(100)
)

insert into @Fruits (string) values ('Apple,Mango'), ('Orange'), ('Apple')

select val,val+'(' +cast(count(val) as varchar(10))+')'val
from @Fruits 
  cross apply dbo.split(string,',') 
group by val

Upvotes: 0

D-Shih
D-Shih

Reputation: 46249

There is a STRING_SPLIT support from sql-server 2016 if your version lower than 2016,you can try to write a split function to split your column by ,

CREATE FUNCTION fn_split ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(',', @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(',', @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END

then use CROSS APPLY to get count by the name.

SELECT Name,count(*)
FROM T t1 CROSS APPLY fn_split(t1.col) v
group by Name

sqlfiddle

Upvotes: 3

Related Questions