Solved

split string t-sql

Posted on 2009-05-14
6
1,321 Views
Last Modified: 2012-06-27
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
Comment
Question by:manivineet
  • 4
  • 2
6 Comments
 
LVL 31

Accepted Solution

by:
RiteshShah earned 400 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

by:RiteshShah
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

Open in new window

0
 
LVL 31

Assisted Solution

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


declare @var varchar(50)
set @var='1125425-1'
select * from dbo.split(@var,'-')
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 5

Expert Comment

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

Open in new window

0
 
LVL 5

Assisted Solution

by:sampipes
sampipes earned 100 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

	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
 
LVL 31

Expert Comment

by:RiteshShah
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now