USE [HP610]
GO
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