Solved

split string t-sql

Posted on 2009-05-14
6
1,330 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

770 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