如何在字符串sql中获取特定字符后的所有字符

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

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.

我可能会把它们分开因为这个结构不是常数。你似乎只想要小数点后的值。这将在逗号上分割值，然后在小数点后得到所有值，然后将其重新组合成一个字符串。

ONLINE DEMO

在线演示

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

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

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

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