substring a nvarchar var that have the same criteria character multiple times

Question

Inside a tsql stored procedure i want to substring 'x' that is nvarchar(max).

x can take values like:

1.000.232 or 2.1110.23444.456

I want to save to 'z',which is also nvarchar(max), only the middle numbers between the first and the last '.'

For example:

if x=1.000.232 then z=000
if x=2.1110.23444.456 then z=1110.23444

The solution would be to stop storing delimited strings in your database. Anything else is only a workaround. For more information, read [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutely yes!** — Zohar Peled, Feb 27 '20 at 18:19

score 1 · Accepted Answer · answered Feb 27 '20 at 18:14

Find the index of the first and last periods and then use the SUBSTRING() function to find the text between them.

DECLARE
    @x                VARCHAR(MAX)
  , @z                VARCHAR(MAX)
  , @FirstPeriodIndex INT
  , @LastPeriodIndex  INT;

--SET @x = '1.000.232'
SET @x = '2.1110.23444.456';

SELECT
    @FirstPeriodIndex = CHARINDEX('.', @x)
  , @LastPeriodIndex  = LEN(@x) - CHARINDEX('.', REVERSE(@x)) + 1;

SELECT @z = SUBSTRING(@x, @FirstPeriodIndex + 1, @LastPeriodIndex - @FirstPeriodIndex - 1);

SELECT
    @x                AS x
  , @FirstPeriodIndex AS FirstPeriodIndex
  , @LastPeriodIndex  AS LastPeriodIndex
  , @z                AS z;

Here is the dbfiddle.

substring a nvarchar var that have the same criteria character multiple times

1 Answers1