Click here to Skip to main content
15,177,017 members
Articles / Programming Languages / T-SQL
Tip/Trick
Posted 11 Sep 2015

Tagged as

Stats

8.9K views
5 bookmarked

Split Any Delimited String in SQL

Rate me:
Please Sign up or sign in to vote.
4.88/5 (5 votes)
11 Sep 2015CPOL
This tip shows how to separate a string with delimited characters.

Introduction

Often we come across such scenarios where we need to pass data as string, for example (Apple, Mango, Orange, Pineapple) to database and then store each item of string in database as separate rows in respective table. Thus it leads us to identify a mechanism to be able to split string based on some delimited characters and use the result accordingly.

Using the Code

With the use of T-SQL to split a delimited string with any delimited characters, the problem can be solved easily.

Create a SQL function as below:

SQL
// sql function which accepts string and delimiter character and return items in table.

CREATE FUNCTION [dbo].[SplitString] (@InputString NVARCHAR(MAX),@delimiter CHAR(1))
RETURNS @tbl TABLE (Item NVARCHAR(50) NOT NULL)
AS
BEGIN
  DECLARE @StartIndex int = 0,
          @NextIndex int = 1,
          @ItemLen int

  WHILE @NextIndex > 0
  BEGIN
    SELECT @NextIndex = CHARINDEX(@delimiter, @InputString, @StartIndex + 1)
    SELECT @ItemLen = CASE
                          WHEN @NextIndex > 0 THEN @NextIndex
                          ELSE LEN(@InputString) + 1
                      END - @StartIndex - 1

    INSERT @tbl (Item)
    VALUES (CONVERT(varchar(50), SUBSTRING(@InputString, @StartIndex + 1, @ItemLen)))
    
    SELECT @StartIndex = @NextIndex

  END

  RETURN

END

Testing the Above SQL Function with Samples Input

Execute the query:

SQL
SELECT * FROM [SplitString]('Apple-Dell-HP-Lenovo-Sony','-')

OUTPUT

  Item
1 Apple
2 Dell
3 HP
4 Lenovo
5 Sony

Points of Interest

I hope the tip was easy to understand and implement.

History

  • 11th September, 2015: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author


Comments and Discussions

 
QuestionThat works, But This Is Better Pin
JonathanFahey17-Sep-15 10:15
MemberJonathanFahey17-Sep-15 10:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.