CODEWITHSUNDEEP
sqlsql-servert-sql
jimmy8ball
jimmy8ball

Reputation: 756

SQL Selecting a value between 2 special characters where more than one special character exists

I have a simple query:

LEFT(ReportMasterID, CHARINDEX(':', ReportMasterID) - 1) AS cons

I need to work out a variation of the script above that will pull back only the value between 2 special chars where there are more than one set of special chars in the string.

Here is the format of the string I need to pull the value from:

BORMG01D:BORMG:111111:1251624:40200

Obviously the above select generates an error because there are more than one set of special chars - I just need this value:

BORMG

Can anyone help please?

Upvotes: 1

Views: 184

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 82020

Here is a simple XML approach

Example

Declare @YourTable table (ID int,ReportMasterID varchar(max))
Insert Into @YourTable values
(1,'BORMG01D:BORMG:111111:1251624:40200')

Select ID
      ,Pos2 = convert(xml,'<x>'+replace(ReportMasterID,':','</x><x>')+'</x>').value('/x[2]','varchar(100)')
 From @YourTable

Returns

ID  Pos2
1   BORMG

Upvotes: 2

Related Questions