With my possibly faulty understanding of the rules (although I think I'm probably pretty close), I came up with this function. As usual, the details, usage example, and some unit test code are all embedded in the function.

CREATE FUNCTION dbo.FirstOccuranceOfAnotBCD

/**********************************************************************************************************************

Purpose:

Return the "word" found at the first occurance of where the letter "A" is found and it's followed by any letter other

than the letters "b", "B", "c", "C", "d", or "D". Note that the code is "accent sensitive".

In other words, this code does a delimited split of the string on the letter "A" and returns the first "word" that

does NOT contain a "b", "B", "c", "C", "d", or "D" as the second letter.

Returns:

Position = Character position of the found word. (BIGINT)

StringLength = Character Length of the found word. (BIGINT)

FoundWord = The actual word that was found according to all the rules above. (VARCHAR(8000))

Programmer's notes.

1. This function is a high performance iTVF (Inline Table Valued Function) being used as an iSF (Inline Scalar

Funtion) that returns a single value like a "normal" user defined function but without the performance problems.

2. This function could be used in the SELECT list of a query using a correlated subquery or it can be used in the

FROM clause of a query. See the usage examples below.

Usage Example and Test:

--===== If the test table already exists, drop it to make reruns in SSMS easier

IF OBJECT_ID('tempdb..#MyHead','U') IS NOT NULL

DROP TABLE #MyHead

;

--===== Create a test table with string examples and expected results

SELECT *

INTO #MyHead

FROM (

SELECT NULL ,NULL UNION ALL

SELECT '' ,NULL UNION ALL

SELECT ' ' ,NULL UNION ALL

SELECT 'A' ,NULL UNION ALL

SELECT 'B' ,NULL UNION ALL

SELECT 'C' ,NULL UNION ALL

SELECT 'D' ,NULL UNION ALL

SELECT 'E' ,NULL UNION ALL

SELECT 'AAA' ,'A' UNION ALL

SELECT 'BBB' ,NULL UNION ALL

SELECT 'CCC' ,NULL UNION ALL

SELECT 'DDD' ,NULL UNION ALL

SELECT 'EEE' ,NULL UNION ALL

SELECT 'EEEA' ,NULL UNION ALL

SELECT 'AAEEE' ,'A' UNION ALL

SELECT 'ABEEE' ,NULL UNION ALL

SELECT 'ACEEE' ,NULL UNION ALL

SELECT 'ADEEE' ,NULL UNION ALL

SELECT 'AEEEE' ,'AEEEE' UNION ALL

SELECT 'AAAABBAEE' ,'A' UNION ALL

SELECT 'ABBAEEAFF' ,'AEE' UNION ALL

SELECT 'ABBACCAFF' ,'AFF' UNION ALL

SELECT 'ACCADDAFF' ,'AFF' UNION ALL

SELECT 'ADDAFFAGG' ,'AFF' UNION ALL

SELECT 'AFFAGGAHH' ,'AFF' UNION ALL

SELECT 'ÂFFAGGAHH' ,'AGG' UNION ALL

SELECT 'AÂÂAGGAHH' ,'AÂÂ' UNION ALL

SELECT 'ABNAGENDRACSURENDRADJITHENDRAXNARENDRABVEERNDARAXDRMNDRAXRVINDRABNAGENDRACSURENDRADJITHEN','AGENDR' UNION ALL

-- ^^^^^^

SELECT 'ABNACENDRACSURENDRADJITHENDRABNARENDRABVEERNDARAXDRMNDRAXRVINDRABNAGENDRACSURENDRADJITHEN','ARENDR'

-- ^^^^^^

)d(pString,Expected)

;

--===== Run the function against all the test examples in the test table.

-- This is also an example of how to use this "iSF" function in the FROM clause.

SELECT tt.*

,fo.*

FROM #MyHead tt

OUTER APPLY dbo.FirstOccuranceOfAnotBCD(pString) fo

;

--===== Run the function against all the test examples in the test table.

-- This is also an example of how to use this "iSF" function in the SELECT list of a query although it only

-- returns the found text. Additional correlated subqueries would need to be added to returnn the other

-- columns of the function, which is why it's usually best to use it in the FROM clause as above, instead.

SELECT tt.*

,FoundString = (SELECT FoundWord FROM dbo.FirstOccuranceOfAnotBCD(pString))

FROM #MyHead tt

;

Revision History:

Rev 00 - 05 Sep 2015 - Jeff Moden

- Initial creation and unit test.

- Reference: http://www.sqlservercentral.com/Forums/Topic1717320-392-1.aspx

**********************************************************************************************************************/

--===== Define the IO of this function

(@pString VARCHAR(8000))

RETURNS TABLE WITH SCHEMABINDING AS

RETURN WITH

E1(N) AS ( --=== Pseudo-Cursor creates up to 10E1 or 10 rows

SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))v(N)) --10E1 or 10 rows

, E4(N) AS ( --=== Pseudo-Cursor creates up to 10E4 or 10,000 rows

SELECT 1 FROM E1 a, E1 b, E1 c, E1 d)

, Tally(N) AS ( --=== Pseudo-Cursor returns 0 to 10,000 sequentially numbered rows (on-the-fly Tally Table)

SELECT TOP(ISNULL(LEN(@pString),0)) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4)

, FindFirst AS ( --=== Pseudo-Cursor finds first and next position of 'A' followed anything but a "B", "C", or "D"

SELECT TOP 1

P = N --Position of "A?"

,NP = ISNULL(NULLIF(CHARINDEX('A',@pString,N+1),0),LEN(@pString)+1) --Next Position of "A?"

FROM Tally

WHERE SUBSTRING(@pString,N,2) LIKE '[Aa][^bBcCdD]' COLLATE Latin1_General_BIN

)

SELECT Position = P

,StringLength = NP-P

,FoundWord = SUBSTRING(@pString,P,NP-P)

FROM FindFirst

;

GO