12,078,795 members (51,256 online)
Rate this:
See more:
I have a string like 1`111`1111#2`222`2222#3`333`3333

I want output like
1
2
3
111
222
333
1111
2222
3333

I didnt get it..
Posted 8-Apr-13 22:34pm
Edited 9-Apr-13 0:13am
v2
Maciej Los 9-Apr-13 6:02am

Rate this:

## Solution 2

See my past answer: How a split a String using delimiter in Sql[^]. Change the code to your needs.
Rate this:

## Solution 1

You can use any of the split functions available from the following link:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648[^]
Rate this:

## Solution 3

```USE [HP610]
GO
/****** Object:  StoredProcedure [dbo].[USP_SampleSplitProc]    Script Date: 04/10/2013 11:19:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
--TO EXECUTE EXEC dbo.USP_SampleSplitProc '1~11~111~1111#2~22~222~2222#3~33~333~3333#4~44~444~4444#'
-- =============================================
ALTER PROCEDURE [dbo].[USP_SampleSplitProc]
@SAMPLESTRING varchar(1000)
AS
BEGIN
SET NOCOUNT ON;
goto start
Start:
DECLARE @IntPos INT
SET @IntPos = CHARINDEX('#', @SAMPLESTRING)

DECLARE @TEMP1 VARCHAR(100),@SEARCHSTRING VARCHAR(100),@TEMP2 VARCHAR(100),@TEMP3 varchar(100),@TEMP4 varchar(100)
DECLARE @STRING VARCHAR(1000)
SET @STRING = @SAMPLESTRING
SET @TEMP4='';
if LEN(@STRING)>0
BEGIN
WHILE @IntPos > 0
BEGIN
if charindex('~',@STRING)>0
BEGIN
SET @SEARCHSTRING = SUBSTRING(@STRING, 1, @IntPos-1)
IF LEN(LTRIM(@SearchString)) > 0
BEGIN
SET @TEMP1=(SELECT LEFT(@STRING,CHARINDEX('~',@STRING)-1))
PRINT @TEMP1
SET @TEMP2=(SELECT SUBSTRING(@STRING, CHARINDEX('~',@STRING)+1, 100))
SET @TEMP3=(SELECT left(@TEMP2, CHARINDEX('#',@TEMP2)-1))
SET @TEMP4=@TEMP4+@TEMP3+'#';
END
SET @STRING = STUFF(@STRING, 1, @IntPos, '')
SET @IntPos = CHARINDEX('#', @STRING)
END
else
if charindex('#',@STRING)>0
BEGIN
SET @SEARCHSTRING = SUBSTRING(@STRING, 1, @IntPos-1)
IF LEN(LTRIM(@SearchString)) > 0
BEGIN
SET @TEMP1=(SELECT LEFT(@STRING,CHARINDEX('#',@STRING)-1))
PRINT @TEMP1
SET @TEMP2=(SELECT SUBSTRING(@STRING, CHARINDEX('#',@STRING)+1, 100))
SET @TEMP4=@TEMP2
END

SET @STRING = STUFF(@STRING, 1, @INTPOS, '')
SET @INTPOS = CHARINDEX('#', @STRING)
END
END
END
IF @TEMP4 <>''
BEGIN
IF CHARINDEX('#',@TEMP4)>0
SET @SAMPLESTRING=@TEMP4
GOTO START;
END
END```

Top Experts
Last 24hrsThis month
 Dave Kreskowiak 715 CHill60 400 Richard MacCutchan 375 ppolymorphe 325 CPallini 275
 Dave Kreskowiak 3,231 OriginalGriff 3,070 Richard MacCutchan 2,584 CPallini 1,772 CHill60 1,579