asked on

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

ASKER CERTIFIED SOLUTION

RiteshShah

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RiteshShah

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

SOLUTION

RiteshShah

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sampipes

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

SOLUTION

sampipes

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RiteshShah

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.