Extract String Between Two Different Characters-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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

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