How to extract string from a text in SQL Server-CodePudding

I have texts like "DBName_TemplateDB_TESTDB01234_document" and "DBName_TemplateDB_TESTDB01234678_document". From both texts need to extract string between second underscore() and last underscore() like "TESTDB01234" and "TESTDB01234678".

Can you please help how to string in SQL Server using SUBSTRING and CHARINDEX?

Example:

Input Text: 'DBName_TemplateDB_TESTDB01234_document' Output: TESTDB01234

Input Text: 'DBName_TemplateDB_TESTDB01234678_document' Output: TESTDB01234678

I tried to extract and it's working only from the first underscore like below.

  declare @Dbname varchar(max) = '#new#-TESTDB01234_document'
  select substring( LEFT(@DbName,charindex('_',@DbName)-1),charindex('TEST',@DbName),len(LEFT(@DbName,charindex('_',@DbName)))-1)

CodePudding user response：

Using charindex

declare @Dbname varchar(max) = 'DBName_TemplateDB_TESTDB01234678_document'

select right(left(@DbName,third - 1), third - second - 1)
from (
  select charindex('_',@DbName,charindex('_',@DbName)   1) second,
         charindex('_',@DbName,charindex('_',@DbName,charindex('_',@DbName)   1)   1) third
) t

CodePudding user response：

Will this work for you?

--OPTION ONE: SUBSTRING
DECLARE @Start INT = CHARINDEX('TEST', @Dbname);
DECLARE @End INT = LEN(@Dbname) - (CHARINDEX('_', REVERSE(@Dbname)) - 1) - @Start;
SELECT SUBSTRING(@Dbname, @Start, @End) AS [Name]

--OPTION TWO: DOUBLE REPLACE
SELECT REPLACE(REPLACE(@Dbname, 'DBName_TemplateDB_', ''), '_document', ''); AS [Name]

--OPTION THREE: STRING_SPLIT
SELECT TOP 1 value AS [Name]
    FROM STRING_SPLIT(@Dbname, '_') 
    WHERE value LIKE 'Test%'

I added a few options, sorry if its outside the scope of the question.

CodePudding user response：

If you are looking for data between the second and the last underscore, I will give the following approach as there might be more underscores between these ones:

declare @Dbname varchar(max) = 'DBName_TemplateDB_TESTDB01234678_1_2_3_document'

DECLARE @DbnameXML XML = '<a>'   REPLACE(@Dbname, '_', '</a><a>')   '</a>';

SELECT STRING_AGG([value], '_') WITHIN GROUP (ORDER BY [value_id])
FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY T.c) - 1
          ,T.c.value('.', 'VARCHAR(128)')
          ,COUNT(1) OVER()
    FROM @DbnameXML.nodes('a') T(c)
) DS ([value_id], [value], [values_count])
WHERE [value_id] > 1
    AND [value_id]   1 < [values_count]