使用storedproceedure拆分字符串 [英] splitting the string using storedproceedure
本文介绍了使用storedproceedure拆分字符串的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个字符串,如1`111`1111#2`222`2222#3`333`3333
我想输出像
1
2
3
111
222
333
1111
2222
3333
请发回答。
我没得到它..
解决方案
查看我过去的回答:如何在Sql中使用分隔符拆分字符串 [ ^ 。根据需要更改代码。
您可以使用以下链接中提供的任何拆分功能:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648 [<一个href =http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648target =_ blanktitle =新窗口> ^ ]
< blockquote>
USE [HP610]
GO
/ ******对象:StoredProcedure [dbo]。[USP_SampleSplitProc]脚本日期:04/10/2013 11:19: 22 ****** /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
- ============ =================================
- 作者:<作者,姓名>
- 创建日期:<创建日期,,>
- 说明:< 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;
转到开始
开始:
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 ='';
如果LEN(@STRING)> 0
BEGIN
WHILE @IntPos> 0
BEGIN
如果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
如果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
如果CHARINDEX('#',@ TEMP4)> 0
SET @ SAMPLESTRING = @ TEMP4
GOTO START;
END
END
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..
解决方案
See my past answer: How a split a String using delimiter in Sql[^]. Change the code to your needs.
You can use any of the split functions available from the following link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648[^]
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
这篇关于使用storedproceedure拆分字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文