Splitting of Column Values

You can, if you are comfortable with dynamic query and using #table instead of @name.

You can use @name as well, but then entire thing should be done in dynamic query.

For example:

declare @dynamicSQL nvarchar(max) = '';

select @dynamicSQL += 'insert into #name

select [split_value] from dbo.split(''' + name + ''', ';');

'

from data_table.

create #name table

execute sp_executeSQL @dynamicSQL

select count query on #name table as before.

Hope it helps!

Hi Nitin,

I need the query without using Cursor

Thanks

Hello,

You can write / download script of a function which splits the text by given delimiter and put the values in a table. And then fire a simple count query. The untested sample code as follows:

create @name table

(

[name] varchar(100)

)

cursor each row in a input table

take column value in a string variable

insert into @name

select [split_value] from dbo.split(@column_value, ';');

end cursor

select [name], count(1)

from @name

group by [name]

order by [name]