Does anyone know the best way I can normalized a string in the below format:-
有没有人知道我能用以下格式规范化字符串的最佳方法: -
'(20111026,1000,34.10)(20111027,1000,44.10)(20111028,1000,54.10)(20111029,1000,64.10)(20111030,1000,74.10)'
Into 5 rows with 3 columns?
分为5行3列?
|Date |Time |Amount|
-------------------------------
|2011-10-26 |10:00:00 |34.10 |
|2011-10-27 |10:00:00 |44.10 |
|2011-10-28 |10:00:00 |54.10 |
|2011-10-29 |10:00:00 |64.10 |
|2011-10-30 |10:00:00 |74.10 |
I have managed to do this using a string parser with delimiter of ')(' to get the rows and ',' again to get the columns. However when I do this against 7 million strings the db blow out.
我已经设法使用字符串解析器和分隔符')('获取行和','再次获取列。但是当我对700万字符串执行此操作时,数据库爆炸。
This is the SQL I have got so far:
这是我到目前为止的SQL:
DECLARE @Text VARCHAR(500) = '(20111026,1000,34.10)(20111027,1000,44.10)(20111028,1000,54.10)(20111029,1000,64.10)(20111030,1000,74.10)'
SELECT
TRY_CONVERT(DATE, [1]) AS StartDate ,
CAST(TRY_CONVERT(TIME(0), DATEADD(HOUR, ( [2] / 100 ) % 100, DATEADD(MINUTE, ( [2] / 1 ) % 100, CAST('00:00' AS TIME)))) AS VARCHAR(8)) AS StartTime ,
TRY_CONVERT(NUMERIC(16, 6), [3]) AS Amount
FROM
(SELECT
X.Ordinal AS RoNum ,
Y.Ordinal AS ColNum ,
REPLACE(Y.StringValue, '(', '') AS Value
FROM
dbo.ParseString(@Text, ')(') X
CROSS APPLY
dbo.ParseString(StringValue,',') Y
WHERE
NOT Y.StringValue = '') AS SRC
PIVOT
( MIN(Value) FOR ColNum IN ( [1], [2], [3] ) ) AS PVT;
Parse string function:-
解析字符串功能: -
CREATE FUNCTION [dbo].[ParseString]
(@String VARCHAR(500), @Delimiter CHAR(1))
RETURNS TABLE
AS
RETURN
(WITH Results AS
(SELECT 1 AS Ordinal,
LTRIM(LEFT(@String, CHARINDEX(@Delimiter, @String + @Delimiter)-1)) AS StringValue,
CONVERT(VARCHAR(500), RIGHT(@String + @Delimiter, LEN(@String) - CHARINDEX(@Delimiter, @String+@Delimiter) + 1)) AS Remaining
UNION ALL
SELECT Ordinal+1,
LTRIM(LEFT(Remaining, CHARINDEX(@Delimiter, Remaining)-1)),
RIGHT(Remaining, LEN(Remaining) - CHARINDEX(@Delimiter, Remaining))
FROM Results
WHERE LEN(Remaining) > 0)
SELECT Ordinal,
StringValue
FROM Results
)
Any help with this would be much appreciated.
任何帮助都将非常感激。
1 个解决方案
#1
1
There is no need to resort to splitting string for something like this. The format you posted is almost correct to use table value constructors. Using replace to stick commas in between the set of values already wrapped nicely in () means you can do this pretty easily using some dynamic sql.
对于这样的事情,没有必要求助于分裂字符串。您发布的格式几乎正确使用表值构造函数。使用replace在()已经很好地包装的值集之间粘贴逗号意味着你可以使用一些动态的sql轻松地完成这项工作。
declare @String varchar(max) = '(20111026,1000,34.10)(20111027,1000,44.10)(20111028,1000,54.10)(20111029,1000,64.10)(20111030,1000,74.10)'
declare @SQL nvarchar(max)
set @SQL = 'select * from (VALUES ' + REPLACE(@String, ')(', '),(') + ')N (col1, col2, col3)'
select @SQL
exec sp_executesql @SQL
#1
1
There is no need to resort to splitting string for something like this. The format you posted is almost correct to use table value constructors. Using replace to stick commas in between the set of values already wrapped nicely in () means you can do this pretty easily using some dynamic sql.
对于这样的事情,没有必要求助于分裂字符串。您发布的格式几乎正确使用表值构造函数。使用replace在()已经很好地包装的值集之间粘贴逗号意味着你可以使用一些动态的sql轻松地完成这项工作。
declare @String varchar(max) = '(20111026,1000,34.10)(20111027,1000,44.10)(20111028,1000,54.10)(20111029,1000,64.10)(20111030,1000,74.10)'
declare @SQL nvarchar(max)
set @SQL = 'select * from (VALUES ' + REPLACE(@String, ')(', '),(') + ')N (col1, col2, col3)'
select @SQL
exec sp_executesql @SQL