split string t-sql

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

manivineetAsked:
Who is Participating?
 
RiteshShahConnect With a Mentor Commented:
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
 
RiteshShahCommented:
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

Open in new window

0
 
RiteshShahConnect With a Mentor Commented:
you can run above function like:


declare @var varchar(50)
set @var='1125425-1'
select * from dbo.split(@var,'-')
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sampipesCommented:
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','-')

Open in new window

0
 
sampipesConnect With a Mentor Commented:
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
	ADD CONSTRAINT PK_Tally_N 
		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

Open in new window

0
 
RiteshShahCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.