I have a column that contains rows with strings in the following form:
我有一个列包含以下形式的字符串行:
#S6TF3.01,#S6TF3.09,#S6TF3.10,#S6TF3.13
I want to be able to get the following result for all rows:
我希望能够得到所有行的如下结果:
01,09,10,13
I am using tsql and have tried the following:
我正在使用tsql,并尝试了以下步骤:
SUBSTRING(E.REJECT_WF_NOS, CHARINDEX('.', E.REJECT_WF_NOS) + 1, LEN(E.REJECT_WF_NOS))
4 个解决方案
#1
3
I'd probably split these out since the structure isn't constant. You seemingly only want the values after the decimal. This splits the value on the comma, then gets everything after the decimal, then re-concats it into a string.
我可能会把它们分开因为这个结构不是常数。你似乎只想要小数点后的值。这将在逗号上分割值,然后在小数点后得到所有值,然后将其重新组合成一个字符串。
在线演示
declare @var varchar(64) = 'S6TF3.01,#S6TF3.09,#S6TF3.10,#S6TF3.13'
SELECT
STUFF((
SELECT ',' + substring(item,charindex('.',Item) + 1,32)
FROM dbo.DelimitedSplit8K(@var,',')
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
Here Is the Function from Jeff Moden
这是Jeff Moden提供的函数
CREATE FUNCTION [dbo].[DelimitedSplit8K] (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
/* "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
enough to cover VARCHAR(8000)*/
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
;
GO
#2
1
Here is a simple 2016 version if you want it rowbased
这里有一个简单的2016版本,如果你想要它基于行
select reverse(substring(reverse(value),1,2)),* from
string_split('#S6TF3.01,#S6TF3.09,#S6TF3.10,#S6TF3.13',',')
Result multiple rows - 2016:
结果多行- 2016:
* *
If you want it in one row only, this could be the 2016 way
如果你只需要一行,这可能是2016年的方式。
DECLARE @MyTable TABLE
(
ID int ,
Strings varchar(10)
)
INSERT INTO @MyTable (ID,Strings)
select 1,reverse(substring(reverse(value),1,2)) as SplittedValues
from string_split('#S6TF3.01,#S6TF3.09,#S6TF3.10,#S6TF3.13',',')
---Select * from @MyTable
SELECT ID, NewVales = STUFF((SELECT N', ' + Strings
FROM @MyTable AS p2
WHERE p2.ID = p.ID
ORDER BY Strings
FOR XML PATH(N'')), 1, 2, N'')
FROM @MyTable AS p
GROUP BY ID
ORDER BY ID;
Result 1 row - 2016
结果1行- 2016
#3
0
For this you don't even need a splitter; you could use parsename to "split" the values.
你甚至不需要一个分离器;您可以使用parsename来“拆分”值。
declare @string varchar(100) = '#S6TF3.01,#S6TF3.09,#S6TF3.10,#S6TF3.13';
select newString = stuff
(( select ','+substring(item, 1, charindex(',', item)-1)
from (values (4),(3),(2),(1)) t(n)
cross apply (values (substring(@string, charindex('.',@string)+1, 8000)+',')) s(string)
cross apply (values (parsename(string,n))) split(item)
for xml path('')), 1,1,'');
This will be faster than delimitedsplit8k and does not require SQL Server 2016. On SQL Server 2017 you could simplify further like this:
这将比delimitedsplit8k要快,并且不需要SQL Server 2016。在SQL Server 2017上,您可以进一步简化如下:
select string_agg(substring(item, 1, charindex(',', item)-1),',')
from (values (4),(3),(2),(1)) t(n)
cross apply (values (substring(@string, charindex('.',@string)+1, 8000)+',')) s(string)
cross apply (values (parsename(string,n))) split(item)
#4
0
If you have data like row by row like as below Table Structure :
如果您有如下表结构所示的数据:
ID DATA
1 #S6TF3.01
2 #S6TF3.09
3 #S6TF3.10
4 #S6TF3.13
And if you have always data like as above then just write the substring function as below :
如果你总是有如上所述的数据,那就把子字符串函数写成如下:
SELECT SUBSTRING(DATA, 8, 2) DATA from #TEMP
Result:
结果:
ID DATA Result
1 #S6TF3.01 01
2 #S6TF3.09 09
3 #S6TF3.10 10
4 #S6TF3.13 13
#1
3
I'd probably split these out since the structure isn't constant. You seemingly only want the values after the decimal. This splits the value on the comma, then gets everything after the decimal, then re-concats it into a string.
我可能会把它们分开因为这个结构不是常数。你似乎只想要小数点后的值。这将在逗号上分割值,然后在小数点后得到所有值,然后将其重新组合成一个字符串。
在线演示
declare @var varchar(64) = 'S6TF3.01,#S6TF3.09,#S6TF3.10,#S6TF3.13'
SELECT
STUFF((
SELECT ',' + substring(item,charindex('.',Item) + 1,32)
FROM dbo.DelimitedSplit8K(@var,',')
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
Here Is the Function from Jeff Moden
这是Jeff Moden提供的函数
CREATE FUNCTION [dbo].[DelimitedSplit8K] (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
/* "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
enough to cover VARCHAR(8000)*/
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
;
GO
#2
1
Here is a simple 2016 version if you want it rowbased
这里有一个简单的2016版本,如果你想要它基于行
select reverse(substring(reverse(value),1,2)),* from
string_split('#S6TF3.01,#S6TF3.09,#S6TF3.10,#S6TF3.13',',')
Result multiple rows - 2016:
结果多行- 2016:
* *
If you want it in one row only, this could be the 2016 way
如果你只需要一行,这可能是2016年的方式。
DECLARE @MyTable TABLE
(
ID int ,
Strings varchar(10)
)
INSERT INTO @MyTable (ID,Strings)
select 1,reverse(substring(reverse(value),1,2)) as SplittedValues
from string_split('#S6TF3.01,#S6TF3.09,#S6TF3.10,#S6TF3.13',',')
---Select * from @MyTable
SELECT ID, NewVales = STUFF((SELECT N', ' + Strings
FROM @MyTable AS p2
WHERE p2.ID = p.ID
ORDER BY Strings
FOR XML PATH(N'')), 1, 2, N'')
FROM @MyTable AS p
GROUP BY ID
ORDER BY ID;
Result 1 row - 2016
结果1行- 2016
#3
0
For this you don't even need a splitter; you could use parsename to "split" the values.
你甚至不需要一个分离器;您可以使用parsename来“拆分”值。
declare @string varchar(100) = '#S6TF3.01,#S6TF3.09,#S6TF3.10,#S6TF3.13';
select newString = stuff
(( select ','+substring(item, 1, charindex(',', item)-1)
from (values (4),(3),(2),(1)) t(n)
cross apply (values (substring(@string, charindex('.',@string)+1, 8000)+',')) s(string)
cross apply (values (parsename(string,n))) split(item)
for xml path('')), 1,1,'');
This will be faster than delimitedsplit8k and does not require SQL Server 2016. On SQL Server 2017 you could simplify further like this:
这将比delimitedsplit8k要快,并且不需要SQL Server 2016。在SQL Server 2017上,您可以进一步简化如下:
select string_agg(substring(item, 1, charindex(',', item)-1),',')
from (values (4),(3),(2),(1)) t(n)
cross apply (values (substring(@string, charindex('.',@string)+1, 8000)+',')) s(string)
cross apply (values (parsename(string,n))) split(item)
#4
0
If you have data like row by row like as below Table Structure :
如果您有如下表结构所示的数据:
ID DATA
1 #S6TF3.01
2 #S6TF3.09
3 #S6TF3.10
4 #S6TF3.13
And if you have always data like as above then just write the substring function as below :
如果你总是有如上所述的数据,那就把子字符串函数写成如下:
SELECT SUBSTRING(DATA, 8, 2) DATA from #TEMP
Result:
结果:
ID DATA Result
1 #S6TF3.01 01
2 #S6TF3.09 09
3 #S6TF3.10 10
4 #S6TF3.13 13