Click here to Skip to main content
12,078,795 members (51,256 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL Server
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


Please post the answer.
I didnt get it..
Posted 8-Apr-13 22:34pm
Edited 9-Apr-13 0:13am
v2
Comments
Maciej Los 9-Apr-13 6:02am
   
You got 2 answers...
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

See my past answer: How a split a String using delimiter in Sql[^]. Change the code to your needs.
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

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[^]
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

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
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web04 | 2.8.160212.1 | Last Updated 25 Jul 2014
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100