Search code examples
sqlsql-serverstringsubstring

How to extract string from a text in SQL Server

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)
Solution

  • 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.