MYSql Split

CREATE FUNCTION SplitString
(
      @Input NVARCHAR(MAX),
      @Character CHAR(1)
)
RETURNS @Output TABLE (
      Item NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT
      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) – 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END
      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)
            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex – 1)
            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END
      RETURN
END
GO
How to use::
SELECT Item
FROM dbo.SplitString(‘Apple,Mango,Banana,Guava’, ‘,’)
Using Procedure::
CREATE PROCEDURE GetEmployees
      @EmployeeIds VARCHAR(100)
AS
BEGIN
      SELECT FirstName, LastName
      FROM Employees
      WHERE EmployeeId IN(
            SELECT CAST(Item AS INTEGER)
            FROM dbo.SplitString(@EmployeeIds, ‘,’)
      )
END
Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s