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
9 years ago SQL ServerSQL Server, SQL Server Profiler245
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
21,917 views
Notepad Plus Plus Compare Plugin
How To Install Compare Text Plugin In Notepad Plus Plus
19,746 views
Microsoft SQL Server 2008 Attach Remove Log
Delete, Shrink, Eliminate Transaction Log .LDF File
15,583 views
JQuery Allow only numeric characters or only alphabet characters in textbox
13,094 views
C# Read Json From URL And Parse/Deserialize Json
9,557 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
  • Research Paper Writing Service
  • Annotated Bibliography Example – How it Can Help You
  • Essay Writing Online Tips – How to Write Essays Online With Essay Proof Editing
  • Get Research Paper Assistance From Professional Help
  • Customized Essay Writing Agency – Why You Want It
Categories
  • CSharp (45)
  • Facebook Graph API (19)
  • Google API (7)
  • Internet (87)
  • iPhone XCode (8)
  • Javascript (35)
  • Linux (28)
  • MySQL (16)
  • PHP (84)
  • Problem Issue Error (29)
  • Resources (32)
  • SQL Server (25)
  • Timeline (5)
  • Tips And Tricks (141)
  • Uncategorized (62)
Recommended
  • Custom Software Development Company
  • Online Useful Tools
  • Premium Themes
  • VPS
2014 © 4 Rapid Development