How to get the value after a specific amount of dots in a SQL server query result

Question

I have one of the column data in a table as:

123.88568.30959300.001.NBIS.07-28-17.XML

From this data I want a value after 3 dot like 001. The values length will vary. I tried by using substring function but m getting first value only i.e 123.

Answer 1

This works for SQL-Server

DECLARE @str NVARCHAR(MAX)='123.88568.30959300.001.NBIS.07-28-17.XML'
DECLARE @start int = 0
DECLARE @end int = 0

SET @start = CHARINDEX('.', @str, CHARINDEX('.', @str, CHARINDEX('.', @str)+1)+1) +1
SET @end = CHARINDEX('.', @str, @start)

SELECT SUBSTRING(@str, @start, @end - @start)

In MySQL, you can do this.

SET @str = "123.885568.309159300.001.NBIS.07-28-17.XML";
SELECT SUBSTRING_INDEX((SUBSTRING_INDEX(@str, ".", 4)), ".", -1); 

---

<sup>Further reading
- SQL Server CHARINDEX() Function
- SQL Server SUBSTRING() Function - MySQL SUBSTRING_INDEX() Function</sup>