CODEWITHSUNDEEP
sql-servert-sqlsubstringcharindex
Jeremy F.
Jeremy F.

Reputation: 1868

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

Upvotes: 1

Views: 7897

Answers (2)

JamieD77
JamieD77

Reputation: 13949

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), '')))

Upvotes: 4

HABO
HABO

Reputation: 15816

This assumes that a colon and subsequent dot will always be present.

declare @Sample as VarChar(100) =
  'Microsoft.SystemCenter.UserActionManager:ServerName_1.domain.net;ServerName_2.domain.net';

with FiendishThingy as (
  select Substring( @Sample, CharIndex( ':', @Sample ) + 1, Len( @Sample ) - CharIndex( ':', @Sample ) ) as AfterColon )
  select Substring( AfterColon, 1, CharIndex( '.', AfterColon ) - 1 ) as ServerName
    from FiendishThingy;

Upvotes: 0

Related Questions