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
6
Medium Priority
?
1,380 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
[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
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 31

Accepted Solution

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

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 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

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.

688 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