Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# split string t-sql

Posted on 2009-05-14
Medium Priority
1,380 Views
i need to split a string (T-SQL)
1125425-1

i need a function which takes the above string and returns 1125425 and 1 separately

0
Question by:manivineet
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 2

LVL 31

Accepted Solution

RiteshShah earned 1200 total points
ID: 24382347
have a look.

declare @var varchar(50)
set @var='1125425-1'

select substring(@var,1,charindex('-',@var)-1),substring(@var,charindex('-',@var)+1,len(@var))
0

LVL 31

Expert Comment

ID: 24382411
if you need function than here it is.
``````CREATE FUNCTION [dbo].[Split]
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End

Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))

Return
END
``````
0

LVL 31

Assisted Solution

RiteshShah earned 1200 total points
ID: 24382413
you can run above function like:

declare @var varchar(50)
set @var='1125425-1'
select * from dbo.split(@var,'-')
0

LVL 5

Expert Comment

ID: 24386124
Though this function is a tad smaller and doesn't use a loop.
``````IF Exists (SELECT 1 FROM sysobjects where xtype = 'TF' AND name = 'SplitString') BEGIN DROP FUNCTION SplitString END
GO
CREATE FUNCTION dbo.SplitString (@inputString NVARCHAR(max), @separator NCHAR)
RETURNS @output TABLE(FirstString NVARCHAR(MAX), SecondString NVARCHAR(MAX))
AS
BEGIN
INSERT	@output
SELECT	LEFT(@inputString, CHARINDEX(@separator,@inputString)-1), RIGHT(@inputString, LEN(@inputString) - CHARINDEX(@separator,@inputString))
RETURN
END

GO

SELECT * from dbo.SplitString('1234-567','-')
``````
0

LVL 5

Assisted Solution

sampipes earned 300 total points
ID: 24386246
For a non looped version of the one that can cope with any number of separators, returning them on per row rather than one per column a tally table can be used.

Since I found out about the tally table I have found it exceedingly useful in so many places. It's basically a one column table of indexed numbers (mine is from 0 to 1000000) you can then join it to a query you would normally need a loop for.

The function below I use regularly for reporting procedures that I need to pass the results of a multi-select parameter to.
``````/**
*  Tally Table Creation
**/
IF OBJECT_ID('dbo.Tally') IS NOT NULL
BEGIN
DROP TABLE dbo.Tally
END
-- Create and populate at the same time
SELECT	TOP 1000000 --equates to more than 30 years of dates
IDENTITY(INT,0,1) AS N
INTO	dbo.Tally
FROM	Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
-- Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

/**
*  Function to process a separated string.
**/
IF Exists (SELECT 1 FROM sysobjects where xtype = 'TF' AND name = 'ProcessStringArray') BEGIN DROP FUNCTION ProcessStringArray END
GO
CREATE FUNCTION dbo.ProcessStringArray (@inputString NVARCHAR(max), @separator NCHAR)
RETURNS @output TABLE(arrayItem NVARCHAR(MAX))
AS
BEGIN
/**
*  Add start and end separators to the Parameter so we can handle single elements
**/
SET	@inputString = @separator + @inputString + @separator
INSERT	@output
/**
*  Join the Tally table to the string at the character level and when we find a separator
*  insert what's between that separator and the next one
**/
SELECT	SUBSTRING(@inputString,N+1,CHARINDEX(@separator,@inputString,N+1)-N-1)
FROM	dbo.Tally
WHERE	N < LEN(@inputString)
AND		SUBSTRING(@inputString,N,1) = @separator
RETURN
END
``````
0

LVL 31

Expert Comment

ID: 24386653
if user has only one "-" and two value one is before and second is after than don't need loop or any function, he can use my first answer, strait and simple and if he wants, can convert that in function as well.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includâ€¦
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that witâ€¦
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
###### Suggested Courses
Course of the Month6 days, left to enroll