Get nth word in a string delimited by "."

Question

Already have the query to get the first word delimited by ".", I need a new column that displays the 3rd word

SELECT   
    MyColumn, 
    LEFT(MyColumn, CHARINDEX('.', MyColumn) - 1) AS [1st Sequence]
FROM    
    dbo.MyTable

Sample #1:

2345.Z2Z2.A12151.AB4R

Query should return A12151

Sample #2:

STR4.35S982F.X24

Query should return X24

EDIT:

Thanks everyone for sharing your queries. Although the WITH CTE worked, I'm thinking of a simplier query since this will be used as a datasource to my dropdownlist and will always refresh when another dropdownlist changes.

I think this can be done by SUBSTRING. Please see below a query I've found and tested which extracts the 2nd word. I'm currently modifying this to extract the 3rd word. If anyone could help me with this, I would be very greatful:

SELECT MyColumn,
SUBSTRING(MyColumn, CHARINDEX('.', MyColumn) + 1 , CHARINDEX('.', MyColumn, CHARINDEX('.', MyColumn)+1) - (CHARINDEX('.', MyColumn) + 1))
FROM dbo.MyTable

Answer 1

One method would be to use Jeff Moden's DelimitedSplit8k. The advantage of this splitter is it returns the ordinal position of each item, which is vital for your goal. it's also something many others don't return (including SQL Server's STRING_SPLIT). This then becomes very simple:

WITH VTE AS (
    SELECT *
    FROM (VALUES('2345.Z2Z2.A12151.AB4R'),('STR4.35S982F.X24'))V(S))    
SELECT DS.Item
FROM VTE
     CROSS APPLY dbo.DelimitedSplit8K(VTE.S,'.') DS
WHERE DS.ItemNumber = 3;

Answer 2

When you don't have SQL2016 and you are not allowed to create functions or stored procedures you can cast to xml and then select the nth node.

declare @someText nvarchar(max) = 'this is some very interesting text'

select CAST( '<p>'+Replace(rtrim(ltrim(@someText)),' ','</p><p>')+'</p>' AS XML).value('(/p)[5]','nvarchar(MAX)')

result -> interesting

Answer 3

CREATE FUNCTION TextSplit (@Testo VARCHAR(MAX), @Delimiter VARCHAR(128), @Position INT)
RETURNS varchar(max)
AS
BEGIN

        DECLARE @Xml XML
        DECLARE @Output TABLE (ID int IDENTITY(1,1),SplitData varchar(max))
        DECLARE @Result varchar(max)

        SET @Xml = CAST(('<a>'+REPLACE(@Testo,@delimiter,'</a><a>')+'</a>') AS XML)

        INSERT INTO @Output (SplitData)
        SELECT ltrim(rtrim(A.value('.', 'VARCHAR(MAX)')))  FROM @Xml.nodes('a') AS FN(a)
        SET @Result = (SELECT SplitData FROM @Output WHERE ID = @Position)

    RETURN(@Result);
END

Answer 4

I've needed this exact thing, for a pesky log file, that has data needing parsing. I wrote two versions. One with a Tally table and the other without. From what I read, the Tally table version should be faster. Not sure if that's true or not but here is what I ended up with. I must admit much of the code below, for the Tally table, is from another post. If I find it I will reference it.

(SQL SERVER)

CREATE FUNCTION dbo.fn_SplitGetNth (@pSTR VARCHAR(MAX),@pOccurance int,@pDlm VARCHAR(MAX)=',')
RETURNS VARCHAR(MAX)
BEGIN
  DECLARE @vRslt VARCHAR(MAX)

 SET @pSTR=@pDlm + @pSTR + @pDlm;
 SELECT @vrslt=Word FROM
    (
  SELECT SUBSTRING(@pstr,id+1,CHARINDEX(@pDlm,@pSTR,id+1)-id-1)[Word],ROW_NUMBER() OVER (ORDER BY id)[Position]
    FROM Tally where id<LEN(@pstr)
    AND SUBSTRING(@pSTR,ID,1)=@pDlm 
    ) a
    WHERE a.Position=@pOccurance

  RETURN @vRslt;
end

Usage:
SELECT dbo.fn_SplitGetNth('STR4.35S982F.X24',3,'.')

Result:

COLUMN1
-------
X24

Without a Tally Table:

CREATE FUNCTION dbo.fnSplitStringX
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1),
    @nthValue int
) 
RETURNS NVARCHAR(120) 
 AS
BEGIN 
    DECLARE @start INT, @end INT, @cnt INT=0;

    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1
        SET @cnt=@cnt+1
        -- INSERT INTO @output (splitdata)  
        IF (@cnt=@nthValue)
        BEGIN
              RETURN SUBSTRING(@string, @start, @end - @start) 
        END
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)
        
    END 
    RETURN ''
END

Answer 5

In T-SQL you could use a window function:

declare @nthPos int = 2

select MyColumn, 
    (
        select [value] 
        from (
            select row_number() over (order by (select null)) as 'ID', [value] 
            from string_split(MyColumn, '.')
        ) x 
        where ID = @nthPos
    ) as 'nthValue'
from dbo.MyTable

Answer 6

I'd suggest to use Common Table Expressions:

;WITH CTE AS
(
  SELECT 1 AS RowNo, LEFT(MyColumn, CHARINDEX('.', MyColumn)-1) AS Word,
    RIGHT(MyColumn, LEN(MyColumn) - CHARINDEX('.', MyColumn)) AS Remainder
  FROM Dummy
  WHERE CHARINDEX('.', MyColumn) >0
  UNION ALL
  SELECT RowNo +1, LEFT(Remainder, CHARINDEX('.', Remainder)-1) AS Word,
    RIGHT(Remainder, LEN(Remainder) - CHARINDEX('.', Remainder)) AS Remainder
  FROM CTE
  WHERE CHARINDEX('.', Remainder) >0
  UNION ALL
  SELECT RowNo+ 1, Remainder As Word, NULL As Remainder
  FROM CTE
  WHERE CHARINDEX('.', Remainder) = 0
)
SELECT Word
FROM CTE
WHERE RowNo = 3

Result:

X24

A12151

SQLFiddle

For further details, please see: Recursive Queries Using Common Table Expressions

[EDIT#2]

Assuming that you want to get every single word into different column, you have to change last SELECT statement to:

SELECT ID, [1], [2], [3], [4]
FROM (
  SELECT RowNo, Word, ROW_NUMBER() OVER(PARTITION BY RowNo ORDER BY RowNo) AS ID
  FROM CTE
) AS src
PIVOT (MAX(Word) FOR RowNo IN([1], [2], [3], [4])) AS pvt

Result:

ID  1       2           3       4

1   2345    35S982F     A12151  AB4R

2   STR4    Z2Z2        X24     (null)

Answer 7

First, a quick and dirty solution that works in SQL Server 2016 and later is to convert the string to a JSON array by replacing the delimiter with "," and use JSON_VALUE to extract only the Nth value :

select JSON_VALUE('["' + REPLACE('2345.Z2Z2.A12151.AB4R','.','","') + '"]','$[2]')

This returns

A12151

This is dirty because it performs a (possibly heavy) string replacement. This can add up if the function is applied to a lot of rows.

A better solution though would be to not split the string in SQL. The question says

this will be used as a datasource to my dropdownlist and will always refresh when another dropdownlist changes

Parsing for display purposes is the job of the client's data access code, not the database's. In the first place, a field should only contain a single value. If the four values in 2345.Z2Z2.A12151.AB4R are significant they should be stored in 4 different fields, allowing easy querying and indexing.

That said, there are cases where one would want to store Value Objects - data items with values that shouldn't be treated as separate entites. Many ORMs offer support for Value Objects, like EF Core 2.0 and NHibernate.

With a value object, the four values in the string can be mapped into individual properties and used in databinding expressions in the UI. Implementing a Value object is easy, even without ORM support. It could be as easy as creating a class with a constructor that accepts a string and a ToString() override that returns the string to serialize, eg :

class MyComplexInvoice
{
    public string A {get;set;}
    public string B {get;set;}
    public string C {get;set;}
    public string D {get;set;}

    public MyComplexInvoice(string input)
    {
      var items=inmput.Split('.');
      A=items[0];
      ...
    }

    public override string ToString()
    {
        return $"{A}.{B}.{C}.{D}";
    }        

}

A better implementation would use Parse() and TryParse() methods like those found on most built-in types, eg :

class MyComplexInvoice
{
    public string A {get;set;}
    public string B {get;set;}
    public string C {get;set;}
    public string D {get;set;}

    public MyComplexInvoice(string a,string string c,string d)
    {
      A=a ?? throw ArgumentNullException(nameof(a));
      ...
    }

    public static bool TryParse(string input,out MyComplexInvoice inv)
    {
        inv=null;
        if (String.IsNullOrWhitespace(input) return false;

        var items=inmput.Split('.');

        if (items.Length!=4) return false;
        // More validations ....

        inv=new MyComplexInvoice(items[0],items[1],items[2],items[3]);

        return true;            
    }

    public override string ToString()
    {
        return $"{A}.{B}.{C}.{D}";
    }        

}

Answer 8

Please try this-

;WITH CTE AS 
(
    SELECT *
    FROM (VALUES('2345.Z2Z2.A12151.AB4R'),('STR4.35S982F.X24'))V(S)
)
,CTE1 AS
(
    SELECT * , 
    REVERSE(SUBSTRING(REVERSE(S),CHARINDEX('.',REVERSE(S),0)
    ,(CHARINDEX('.', REVERSE(S), CHARINDEX('.',REVERSE(S),0)+1) - CHARINDEX('.',REVERSE(S),0)))) N FROM CTE   
)
SELECT S,SUBSTRING(N,0,LEN(N)) N
FROM CTE1

OUTPUT

S                     N
--------------------- ---------------------
2345.Z2Z2.A12151.AB4R A12151
STR4.35S982F.X24      35S982F

(2 rows affected)

Answer 9

I think you can create your own function for it, pretty much the logic can be like this.

This function I created and modified so we can get any delimited character from any position of sequence and any character of delimiter or character to split each text, so it's kind of multifunction of splitting character.

So this can be very useful in any ocassion of splitting character of text anytime when you need it

CREATE FUNCTION dbo.TextSplit ( @input VARCHAR(MAX), @delimiter VARCHAR, @charPosition INT)
RETURNS varchar
AS
BEGIN

 DECLARE @textsearch NVARCHAR(255)
 DECLARE @pos INT
 DECLARE @ loop INT

SET @loop = 1

 WHILE CHARINDEX(@delimiter, @input) > 0
 BEGIN

  SELECT @pos  = CHARINDEX(@delimiter , @input)  

  SELECT @textsearch = SUBSTRING(@input, 1, @pos-1)

  SELECT @input = SUBSTRING(@input, @pos+1, LEN(@input)-@pos)

  IF @loop = @charPosition
      SET @result = @textsearch;

  SET @loop = @loop + 1
 END

 RETURN(@result);
END

In order to execute it, you can do it like

SELECT   MyColumn
        ,LEFT(MyColumn, CHARINDEX('.', MyColumn) - 1) AS [1st Sequence]
       ,TextSplit(MyColumn, '.', 3) as thirdsequence
FROM    dbo.MyTable

So you can get any delimited character from any position of sequence and any character of delimiter, so it's kind of multifunction of splitting character

Answer 10

There is a work around by using the PARSENAME, but the limitation it will not support, if you have more than three dots in the string.

Query execution using sample data:

DECLARE @SampleTable TABLE (TextValue VARCHAR (2000));

INSERT INTO @SampleTable (TextValue)
VALUES ('2345.Z2Z2.A12151.AB4R'), ('STR4.35S982F.X24'), ('123.345'), ('12345'), (NULL);

SELECT  CASE (LEN(TextValue) - LEN(REPLACE(TextValue, '.', '')))
        WHEN 3 THEN PARSENAME(TextValue, 2)
        WHEN 2 THEN PARSENAME(TextValue, 1)
        ELSE TextValue
        END AS TextValue
FROM @SampleTable