Solved

split string t-sql

Posted on 2009-05-14
6
1,309 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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 …
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…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

706 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

21 Experts available now in Live!

Get 1:1 Help Now