Search code examples
sql-servert-sqlsubstringcharindex

How to extract strings between two special different characters in TSQL

I am using the code from the following question: How to extract strings between two special characters in TSQL

DECLARE @c varchar(100)
SET     @c = 'Microsoft.SystemCenter.UserActionManager:ServerName_1.domain.net;ServerName_2.domain.net' 

SELECT SUBSTRING(@c, CHARINDEX(':', @c) + 1, LEN(@c) - CHARINDEX('.', @c) - CHARINDEX(':', @c))

Current Result:

ServerName_1.domain.net;ServerName_2.

I want to search a column/string and extract the string starting at the ':' and going to the first '.'.

The result I am trying to get is: ServerName_1

The caveat is that the server name will vary in length.

Also, when I try to run the following query:

SELECT 
SUBSTRING(Column_Name, CHARINDEX(':', Column_Name) + 1, LEN(Column_Name) - CHARINDEX(':', Column_Name) - CHARINDEX(':', Column_Name))
FROM [dbo].[ServerNameTable]

I get the following error:

Msg 537, Level 16, State 3, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

Many thanks in advance.

SQL Fiddle

Solution

  • This will replace everything up to and including the first : using stuff. Then it uses that same result to find the first . and substring the stuff result up to the first .

    DECLARE @c varchar(100)
SET     @c = 'Microsoft.SystemCenter.UserActionManager:ServerName_1.domain.net;ServerName_2.domain.net'

SELECT SUBSTRING(STUFF(@c, 1, CHARINDEX(':',@c), ''), 0, CHARINDEX('.', STUFF(@c, 1, CHARINDEX(':',@c), '')))