1st you need to create a function in sql server db
Alter FUNCTION mySplitString
(
@myValue nvarchar(Max),
@SplitChar nvarchar(1),
@NoOfPositions int
)
RETURNS nvarchar(1000)
AS
BEGIN
-- Declare the return variable here
DECLARE @name NVARCHAR(500)
Declare @ReturnValue nvarchar(1000);
DECLARE @pos INT
set @ReturnValue=''
SELECT @pos = CHARINDEX(@SplitChar, @myValue)
-- if no split char exist
if @pos <0 begin
set @ReturnValue = @myValue
end
WHILE CHARINDEX(',', @myValue) > 0 And @NoOfPositions>0
BEGIN
SELECT @pos = CHARINDEX(@SplitChar, @myValue)
SELECT @name = SUBSTRING(@myValue, 1, @pos-1)
if len(@ReturnValue) > 0
begin
Set @ReturnValue = @ReturnValue + @SplitChar + @name
end
else
begin
Set @ReturnValue = @name
end
set @NoOfPositions = @NoOfPositions-1;
SELECT @myValue = SUBSTRING(@myValue, @pos+1, LEN(@myValue)-@pos)
END
--If string less then no of positions
if (@NoOfPositions >0) begin
if len(@ReturnValue) > 0
begin
Set @ReturnValue = @ReturnValue + @SplitChar + REPLACE(@myValue,@ReturnValue,'')
end
else
begin
Set @ReturnValue = REPLACE(@myValue,@ReturnValue,'')
end
end
return @ReturnValue;
END
GO
Now Call like this
Select distinct dbo.mySplitString(Categories,',',3) as Categories
from invProductHead
Order by Categories