如何使用存储过程在sql server 2008中拆分字符串并将数据插入表 [英] How to split a string in sql server 2008 using stored procedure and insert the data to table
问题描述
我想以这种格式拆分字符串
Quote:date = 10/10/2000 | age = 13 ^ date = 01 / 01/2001 | age = 12 ^ date = 02/02 / 2005 | age = 8
。
实际上这个字符串只是我原始字符串非常大的样本。我没有得到一个观点,如果我打破这个字符串,而不是我需要做多少变量来捕获数据aslo分裂字符串后我希望将其插入到包含列作为日期和年龄的数据表中?我使用什么概念?(我从Web服务获取此字符串)在此先感谢..
我更喜欢使用常用表格表达式 [ ^ ]。
看看这里:
DECLARE @ s VARCHAR ( 300 )
SET @ s = ' date = 10/10/2000 | age = 13 ^ date = 01/01/2001 | age = 12 ^ date = 02/02 / 2005 | age = 8'
DECLARE @ tmp 表(aDate DAT ETIME ,aAge INT )
; WITH MyRows AS
(
SELECT LEFT ( @ s ,CHARINDEX(' ^', @ s ) - 1) AS MyRow, RIGHT ( @ s ,LEN( @ s ) - CHARINDEX(' ^', @ s )) AS 剩余
UNION 所有
SELECT LEFT (剩余,CHARINDEX(' ^',剩余)-1) AS MyRow, RIGHT (剩余,LEN(剩余) - CHARINDEX(' ^',剩余)) AS 剩余
FROM MyRows
WHERE CHARINDEX(' ^',剩余)> 0
UNION ALL
SELECT 剩余 AS MyRow, NULL AS 剩余
FROM MyRows
WHERE CHARINDEX(' ^',剩余)= 0
)
INSERT INTO @ tmp (aDate,aAge)
SELECT CONVERT ( DATETIME ,SUBSTRING(MyRow,< span class =code-digit> 6 , 10 )) AS aDate,< span class =code-keyword> CONVERT ( INT ,SUBSTRING(MyRow, 21 ,LEN(MyRow))) AS aAge
FROM MyRows
SELECT *
FROM @ tmp
结果:
2000-10-10 00: 00:00.000 13
2001-01-01 00:00:00.000 12
2005-02-02 00:00:00.000 8
< blockquote>参见:
SQL Server - 将分隔的字符串拆分为单独的列 [ ^ ]
另见:
CP文章:用于定义分隔字符串的SQL用户定义函数 [ ^ ]
SO问题:在SQL中拆分字符串 [ ^ ]
祝你好运,
Edo
创建UDF并在存储过程中使用
CREATE FUNCTION [dbo]。[fnSplitString]
(
@ string NVARCHAR (MAX),
@ delimiter CHAR ( 1 )
)
RETURNS @ output TABLE (splitdata NVARCHAR (MAX)
)
BEGIN
DECLARE @start INT , @ end INT
SELECT @start = 1 , @ end = CHARINDEX( @ delimiter , @ string )
WHILE @ start < LEN( @ string )+ 1 BEGIN
IF @ end = 0
SET @ end = LEN( @ string )+ 1
INSERT INTO @ output (splitdata)
VALUES (SUBSTRING) ( @ string , @ start , @ end - @ start ))
SET @开始 = @ end + 1
SET @ end = CHARINDEX ( @ delimiter , @ string , @ start )
END
返回
END
除了这个T-sql语句以创建函数并用作
* -string>' 查询SQL Server',' ')
见
UDF
I want to split a string in this format
Quote:"date=10/10/2000|age=13^date=01/01/2001|age=12^date=02/02/2005|age=8"
.
Actually this string is only a sample one my original string is very large . i am not getting a point that if i break this string than how many variables i have to make to capture the data aslo after splitting the string i want that to be inserted into datatable containing columns as date and age? What concept do i use?(I am getting this string from a web service) Thanks in advance..
I prefer to use Common Table Expressions[^].
Have a look here:
DECLARE @s VARCHAR(300) SET @s = 'date=10/10/2000|age=13^date=01/01/2001|age=12^date=02/02/2005|age=8' DECLARE @tmp TABLE(aDate DATETIME, aAge INT) ;WITH MyRows AS ( SELECT LEFT(@s, CHARINDEX('^', @s) -1) AS MyRow, RIGHT(@s, LEN(@s) - CHARINDEX('^', @s)) AS Remainder UNION ALL SELECT LEFT(Remainder, CHARINDEX('^', Remainder) -1) AS MyRow, RIGHT(Remainder, LEN(Remainder) - CHARINDEX('^', Remainder)) AS Remainder FROM MyRows WHERE CHARINDEX('^', Remainder)>0 UNION ALL SELECT Remainder AS MyRow, NULL AS Remainder FROM MyRows WHERE CHARINDEX('^', Remainder)=0 ) INSERT INTO @tmp (aDate, aAge) SELECT CONVERT(DATETIME, SUBSTRING(MyRow, 6, 10)) AS aDate, CONVERT(INT,SUBSTRING(MyRow, 21, LEN(MyRow))) AS aAge FROM MyRows SELECT * FROM @tmp
Result:
2000-10-10 00:00:00.000 13 2001-01-01 00:00:00.000 12 2005-02-02 00:00:00.000 8
See:
SQL Server - Split delimited string into separate columns[^]
And see also:
CP Article: SQL User Defined Function to Parse a Delimited String[^]
SO Question: Split string in SQL[^]
Good luck,
Edo
Create UDF and use in Stored Procedure
CREATE FUNCTION [dbo].[fnSplitString] ( @string NVARCHAR(MAX), @delimiter CHAR(1) ) RETURNS @output TABLE(splitdata NVARCHAR(MAX) ) BEGIN DECLARE @start INT, @end INT SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) WHILE @start < LEN(@string) + 1 BEGIN IF @end = 0 SET @end = LEN(@string) + 1 INSERT INTO @output (splitdata) VALUES(SUBSTRING(@string, @start, @end - @start)) SET @start = @end + 1 SET @end = CHARINDEX(@delimiter, @string, @start) END RETURN END
Exceute this T-sql statements to create function and use as
·select *from dbo.fnSplitString('Querying SQL Server','')
See
UDF
这篇关于如何使用存储过程在sql server 2008中拆分字符串并将数据插入表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!