I am trying to find all string entries with a lowercase 'a' at index 0 followed by any uppercase at index 1. I've tried a few different ways but this is as close as I can get. I've searched the forums but cannot find anything that fits. Any pointers are greatly appreciated.
Thanks
WITH CTE AS(
select c1.namesurname AS surname,
PATINDEX ('t[A-Z]%', c1.namesurname) AS b,
SUBSTRING(c1.namesurname, 1, 1) as bob,
SUBSTRING(c1.namesurname, 2, 1) as bobs
from core_patient c1)
select * FROM CTE
where
(
bob = 'a' Collate Latin1_General_CS_AS
)