CODEWITHSUNDEEP
sqlsubstring
Eric R.
Eric R.

Reputation: 1125

Get SQL Substring After a Certain Character but before a Different Character

I have some key values that I want to parse out of my SQL Server table. Here are some examples of these key values:

R50470B50469

B17699C88C68AM

R22818B17565C32G16SU

B1444

What I am wanting to get out of the string, is all the numbers that occur after the character 'B' but before any other letter character if it exists such as 'C'. How can I do this in SQL?

Upvotes: 3

Views: 1846

Answers (1)

Martin Smith
Martin Smith

Reputation: 453057

WITH VALS(Val) AS
(
SELECT 'R50470B50469' UNION ALL
SELECT 'R22818B17565C32G16SU' UNION ALL
SELECT 'R22818B17565C32G16SU' UNION ALL
SELECT 'B1444'
)
SELECT SUBSTRING(Tail,0,PATINDEX('%[AC-Z]%', Tail))
FROM   VALS
       CROSS APPLY 
        (SELECT RIGHT(Val, LEN(Val) - CHARINDEX('B', Val)) + 'X') T(Tail)
WHERE  Val LIKE '%B%'

Upvotes: 2

Related Questions