显示特定字符后的字符串。

Consider A string has multiple dots but I want to read and display from 5th dot(.) to end of the string.Any single select query can you suggest. Eg:
I/P

假设一个字符串有多个点，但是我想要从第5点(.)到字符串的末尾进行读取和显示。您可以建议任何一个select查询。例如:我/ P

We.are.inserting.a.lot.of.records by using SSIS Package.even.the.records are.not committed.

o/P

o / P

of.records by using SSIS Package.even.the.records are.not committed.

4 个解决方案

#1

Using CHARINDEX, one method:

CHARINDEX,使用一个方法:

DECLARE @String varchar(500);
SET @String = 'We.are.inserting.a.lot.of.records by using SSIS Package.even.the.records are.not committed.';

SELECT STUFF(@string, 1, CI5.CI,'')
FROM (VALUES(CHARINDEX('.',@String))) CI1(CI)
     CROSS APPLY (VALUES(CHARINDEX('.',@String, CI1.CI+1))) CI2(CI)
     CROSS APPLY (VALUES(CHARINDEX('.',@String, CI2.CI+1))) CI3(CI)
     CROSS APPLY (VALUES(CHARINDEX('.',@String, CI3.CI+1))) CI4(CI)
     CROSS APPLY (VALUES(CHARINDEX('.',@String, CI4.CI+1))) CI5(CI);

Returns: 'of.records by using SSIS Package.even.the.records are.not committed.'

返回:”。使用SSIS包进行记录。记录。不承诺。

#2

Nesting the CHARINDEX:

嵌套CHARINDEX:

SELECT 
  STUFF(val,1, charindex('.',val,charindex('.',val,charindex('.',val,charindex('.',
   val,charindex('.',val)+1)+1)+1)+1), '')
FROM (values('.2.3.4.5.6.7'),('2.3.4.5.6.7'),('abc')) x(val)

This will return the whole string, when the string doesn't contain 5 dots.

当字符串不包含5个点时，它将返回整个字符串。

#3

You can call a recursice CTE for rescue:

您可以调用递归CTE进行营救:

DECLARE @yourString NVARCHAR(MAX)='We.are.inserting.a.lot.of.records by using SSIS Package.even.the.records are.not committed.';
DECLARE @CountDots INT=5;

WITH recCTE AS
(
    SELECT @yourString AS Original
          ,CHARINDEX('.',@yourString) AS PosDot
          ,1 AS DotCount
    UNION ALL

    SELECT r.Original
          ,CHARINDEX('.',@yourString,r.PosDot+1)
          ,r.DotCount+1
    FROM recCTE AS r
    WHERE r.DotCount<@CountDots
)
SELECT SUBSTRING(@yourString,(SELECT MAX(PosDot) FROM recCTE)+1,LEN(@yourString))

One advantage is that you can define the count of dots dynamically. Another advantage is that you can fully inline this to any query, VIEW or iTVF.

一个优点是您可以动态地定义点的计数。另一个优点是可以将其完全内联到任何查询、视图或iTVF。

UPDATE: Set-based approach

DECLARE @yourStringTable TABLE(ID INT IDENTITY,SomeString NVARCHAR(MAX));
INSERT INTO @yourStringTable VALUES 
 ('We.are.inserting.a.lot.of.records by using SSIS Package.even.the.records are.not committed.')
,('1.2.3.4.5.6.7.8');
DECLARE @CountDots INT=5;

WITH recCTE AS
(
    SELECT ID
          ,SomeString AS Original
          ,CHARINDEX('.',SomeString) AS PosDot
          ,1 AS DotCount
    FROM @yourStringTable
    UNION ALL

    SELECT r.ID
          ,r.Original
          ,CHARINDEX('.',r.Original,r.PosDot+1)
          ,r.DotCount+1
    FROM recCTE AS r
    WHERE r.DotCount<@CountDots
)
SELECT ID
      ,Original
      ,SUBSTRING(Original,(SELECT MAX(x.posDot) FROM recCTE AS x WHERE x.ID=recCTE.ID)+1,LEN(Original))
FROM recCTE
WHERE PosDot=(SELECT MAX(x.posDot) FROM recCTE AS x WHERE x.ID=recCTE.ID)

#4

For the said string values only patindex() function would be sufficient to read string after few dot(.)s with substring() function

对于上述字符串值，只有patindex()函数可以在使用substring()函数的点(.)s之后读取字符串

select 
        substring(I/P, patindex('%[.A-Z].[A-Z][.A-Z].[A-Z]%', I/P)+2, LEN(I/P)) [O/P]
from table