CODEWITHSUNDEEP
sqlsql-serverselectsubstring
Steve Lindo
Steve Lindo

Reputation: 11

SQL select data between two characters in a string

I have data shown below. I'd like to retrieve the data between the underscores. The data either side of the underscore can be any length. I am select this data from a table with thousands of rows and will be amount other data being selected in the overall query. Can anyone help please.

Column name is d.ThirdPartyRef 

2500_NEW_001

I have already selected the data from the left of the above field for another column using the below.

substring(d.ThirdPartyRef, 1, charindex('_', d.ThirdPartyRef)-1) as CarrierCode_DayNumber

Thanks

Upvotes: 1

Views: 2278

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81970

You can use ParseName()

Declare @YourTable table (ID int,ThirdPartyRef varchar(500))
Insert Into @YourTable values 
(1,'2500_NEW_001'),
(2,'2500_OLD_002')

Select A.*
      ,Pos1=ParseName(Replace(ThirdPartyRef,'_','.'),3)
      ,Pos2=ParseName(Replace(ThirdPartyRef,'_','.'),2)
      ,Pos3=ParseName(Replace(ThirdPartyRef,'_','.'),1)
 From  @YourTable A

Returns

ID  ThirdPartyRef   Pos1    Pos2    Pos3
1   2500_NEW_001    2500    NEW     001
2   2500_OLD_002    2500    OLD     002

Upvotes: 2

3N1GM4
3N1GM4

Reputation: 3351

If you can guarantee that the values will contain exactly two underscores:

DECLARE @ThirdPartyRef VARCHAR(50)
SET @ThirdPartyRef = '2500_NEW_001'

SELECT  @ThirdPartyRef AS Value,
        CHARINDEX('_',@ThirdPartyRef) AS FirstUnderscore,
        CHARINDEX('_',@ThirdPartyRef,CHARINDEX('_',@ThirdPartyRef)+1) AS SecondUnderscore,
        SUBSTRING(@ThirdPartyRef, CHARINDEX('_',@ThirdPartyRef) + 1, CHARINDEX('_',@ThirdPartyRef,CHARINDEX('_',@ThirdPartyRef)+1) - CHARINDEX('_',@ThirdPartyRef) - 1) AS Result

Results:

/------------------------------------------------------------\
|     Value    | FirstUnderscore | SecondUnderscore | Result |
|--------------+-----------------+------------------+--------|
| 2500_NEW_001 |        5        |         9        |  NEW   |
\------------------------------------------------------------/

The FirstUnderscore and SecondUnderscore columns are just there to illustrate how the solution works.

Upvotes: 0

Related Questions