'Extract String Between Two Different Characters

I am having some trouble trying to figure out a way to extract a string between two different characters. My issue here is that the column (CONFIG_ID) contains more that 75,000 rows and the format is not consistent, so I cannot figure out a way to get the numbers between E and B.

*CONFIG_ID*
6E15B1P
999E999B1P
1E3B1P
1E30B1P
5E24B1P
23E6B1P
sql


Solution 1:[1]

Another option is to use a CROSS APPLY to calculate the values only once. Another nice thing about CROSS APPLY is that you can stack calculations and use them in the top SELECT

Notice the nullif() rather than throwing an error if the character is not found, it will return a NULL

THIS ALSO ASSUMES there are no LEADING B's

Example

Declare @YourTable Table ([CONFIG_ID] varchar(50))  Insert Into @YourTable Values 
 ('6E15B1P')
,('999E999B1P')
,('1E3B1P')
,('1E30B1P')
,('5E24B1P')
,('23E6B1P')
,('23E6ZZZ') -- Notice No B

 
Select [CONFIG_ID]
      ,NewValue = substring([CONFIG_ID],P1,P2-P1)
 From @YourTable
 Cross Apply ( values (nullif(charindex('E',[CONFIG_ID]),0)+1
                      ,nullif(charindex('B',[CONFIG_ID]),0)
                      ) )B(P1,P2)

Results

CONFIG_ID   NewValue
6E15B1P     15
999E999B1P  999
1E3B1P      3
1E30B1P     30
5E24B1P     24
23E6B1P     6
23E6ZZZ     NULL  -- Notice No B

Solution 2:[2]

SUBSTRING(config_id,PATINDEX('%E%',config_id)+1,PATINDEX('%B%',config_id)-PATINDEX('%E%',config_id)-1)

As in:

WITH dat
AS
(
SELECT config_id
FROM (VALUES ('1E30B1P')) t(config_id)
) 
SELECT SUBSTRING(config_id,PATINDEX('%E%',config_id)+1,PATINDEX('%B%',config_id)-PATINDEX('%E%',config_id)-1)
FROM dat

Solution 3:[3]

A cased substring of a left could be enough.

select *
, CASE 
  WHEN [CONFIG_ID] LIKE '%E%B%'
  THEN SUBSTRING(LEFT([CONFIG_ID], CHARINDEX('B',[CONFIG_ID],CHARINDEX('E',[CONFIG_ID]))),
                 CHARINDEX('E',[CONFIG_ID]), LEN([CONFIG_ID]))
  END AS [CONFIG_EB]
from Your_Table
CONFIG_ID CONFIG_EB
6E15B1P E15B
999E999B1P E999B
1E3B1P E3B
1E30B1P E30B
5E24B1P E24B
23E6B1P E6B
23E678 null
236789 null
23B456 null

Test on db<>fiddle here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 John Cappelletti
Solution 2 FlexYourData
Solution 3

Related Questions