Solved

split string t-sql

Posted on 2009-05-14
6
1,369 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 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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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 setup several different housekeeping processes for a SQL Server.

627 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