更新时间:2023-11-13 19:55:34
我更喜欢使用常用表格表达式 [ ^ ]。
看看这里: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, 6 , 10 )) AS aDate, 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
参见:
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 ProcedureCREATE 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