Truemag

  • Categories
    • Tips And Tricks
    • Internet
    • PHP
    • Javascript
    • CSharp
    • SQL Server
    • Linux
  • Lastest Videos
  • Our Demos
  • About
  • Contact
  • Home
  • Write With Us
  • Job Request
Home SQL Server MSSQL Split Function returns table data

MSSQL Split Function returns table data

As we know SPLIT() is very popular function in many languages but unfortunately SQL Server 2000, 2005, 2008 does not have function SPLIT(), they just support some basic functions such as :RTRIM, LTRIM,SUBSTRING,LEFT,RIGHT...And due to these function we can easily create UDF(User Defined Function) as SPLIT() which we would like to mention in this article.

About split string function please refer to this link which is posted by HoanHuynh for more detailed : MSSQL Trim Function
The limitation of this function is that it just supports delimiter with amount to be one.
Now, we would like to create a more advanced SPLIT function which is used to split any string containing delimiter with amount more than one.This function returns data with “Table” type.

For example :
Input    : a string like this ‘HoanHuynh,QuynhNguyen,4rapiddev.com’
Output :

SPLIT_FUNCTION_DATA_RESULT

SPLIT_FUNCTION_DATA_RESULT

The solution is that we create a UDF named fSplit as below :

CREATE FUNCTION dbo.fSplit
(
	@DATA VARCHAR(MAX),
	@delimiter VARCHAR(5)
)
RETURNS @tblReturn TABLE
(
	Id INT IDENTITY(1,1),
	VALUE nvarchar(1000)
)
AS
BEGIN
   DECLARE @delimiterIdx INT -- Index of delimiter in data
 
    SET @delimiterIdx  = Charindex(@delimiter,@DATA)
	While (@delimiterIdx > 0)
	BEGIN
		INSERT INTO @tblReturn(VALUE)
		SELECT  Val = LEFT(@DATA, @delimiterIdx - 1)
 
		SET @DATA = SUBSTRING(@DATA,@delimiterIdx + 1,Len(@DATA))
 
		SET @delimiterIdx  = Charindex(@delimiter,@DATA) -- get next index of delimiter
	END
 
	INSERT INTO @tblReturn (VALUE)
	SELECT Val = Ltrim(Rtrim(@DATA))
 
	RETURN
END

CREATE FUNCTION dbo.fSplit ( @data varchar(max), @delimiter varchar(5) ) RETURNS @tblReturn table ( Id int identity(1,1), Value nvarchar(1000) ) AS BEGIN Declare @delimiterIdx int -- Index of delimiter in data Set @delimiterIdx = Charindex(@delimiter,@data) While (@delimiterIdx > 0) Begin Insert Into @tblReturn(Value) Select Val = Left(@data, @delimiterIdx - 1) Set @data = Substring(@data,@delimiterIdx + 1,Len(@data)) Set @delimiterIdx = Charindex(@delimiter,@data) -- get next index of delimiter End Insert Into @tblReturn (Value) Select Val = Ltrim(Rtrim(@data)) Return END

After create this function, execute the below SQL statement:

SELECT * FROM dbo.fSplit('HoanHuynh,QuynhNguyen,4rapiddev.com',',')

Select * from dbo.fSplit('HoanHuynh,QuynhNguyen,4rapiddev.com',',')

We get expected result as the above image  :).

Feb 11, 2012quynhnguyen
Change Default Port For CollabNet Subversion EdgeVisual Studio Auto generate comments using GhostDoc plugin
You Might Also Like:
  • MSSQL Split String Function
  • MSSQL Export A Table Data To CSV
  • MSSQL Trim Function
  • C# Get File Extension Without Sub String Or Split Function
  • SQL SERVER How to import Text/CSV file into table
  • Replace String With NText Or Text Data Type In MS SQL Server
  • How To Track Website With Multiple Google Analytisc Accounts
  • MSSQL Create Database With SQL Statement
  • Javascript Problem Set focus textbox on Firefox
  • Where MySQL Data Dir And Innodb Data Home Dir Location
quynhnguyen
10 years ago SQL ServerSQL Server, SQL Server Profiler280
0
GooglePlus
0
Facebook
0
Twitter
0
Digg
0
Delicious
0
Stumbleupon
0
Linkedin
0
Pinterest
Most Viewed
PHP Download Image Or File From URL
24,558 views
Notepad Plus Plus Compare Plugin
How To Install Compare Text Plugin In Notepad Plus Plus
21,892 views
Microsoft SQL Server 2008 Attach Remove Log
Delete, Shrink, Eliminate Transaction Log .LDF File
17,746 views
JQuery Allow only numeric characters or only alphabet characters in textbox
15,071 views
C# Read Json From URL And Parse/Deserialize Json
11,805 views
4 Rapid Development is a central page that is targeted at newbie and professional programmers, database administrators, system admin, web masters and bloggers.
Recent Posts
  • Things to Learn about Installingderm Loan Type S
  • Online Photo Editor – Free Photoediting Software
  • A Guide to Finding the Best Paper Sellers
  • Photoediting in Home Isn’t Hard to Do!

  • Free Photo Editor Online
Categories
  • CSharp (45)
  • Facebook Graph API (19)
  • Google API (7)
  • Internet (87)
  • iPhone XCode (8)
  • Javascript (35)
  • Linux (27)
  • MySQL (16)
  • PHP (84)
  • Problem Issue Error (29)
  • Resources (32)
  • SQL Server (25)
  • Timeline (5)
  • Tips And Tricks (141)
  • Uncategorized (647)
Recommended
  • Custom Software Development Company
  • Online Useful Tools
  • Premium Themes
  • VPS
2014 © 4 Rapid Development