Split and count the values merged with delimiters
10,272
Solution 1
If you need to count the number of occurances, you can use this (SQL Server):
SELECT LEN(yourColumn) - LEN(REPLACE(yourColumn, 'b', '')) FROM yourTable
Solution 2
I'm on SQL-Server 2005 and use this Split
function:
SELECT split.Item, count=Count(*)
FROM dbo.Foo f
CROSS APPLY dbo.Split(f.ColName, '|')split
GROUP BY split.Item
Having split.Item='b'
Here the table valued function:
CREATE FUNCTION [dbo].[Split]
(
@ItemList NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50))
AS
BEGIN
DECLARE @tempItemList NVARCHAR(MAX)
SET @tempItemList = @ItemList
DECLARE @i INT
DECLARE @Item NVARCHAR(4000)
SET @tempItemList = REPLACE (@tempItemList, ' ', '')
SET @i = CHARINDEX(@delimiter, @tempItemList)
WHILE (LEN(@tempItemList) > 0)
BEGIN
IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)
INSERT INTO @IDTable(Item) VALUES(@Item)
IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
SET @i = CHARINDEX(@delimiter, @tempItemList)
END
RETURN
END
Related videos on Youtube
Author by
Candy
Updated on September 15, 2022Comments
-
Candy over 1 year
I have a|b|c|d|.. values in a cell. How can I use to count the total occurrence of b in all the calls by splitting it with the help of delimiter
-
Ahmad Nawaz almost 6 yearsWorked for me. :)