Search code examples
stringt-sqluppercaselowercase

Search strings that start with lowercase followed by uppercase

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
)
Solution

  • You can use Latin1_General_BIN Collation in a WHERE LIKE clause like so:

    DECLARE @core_patient TABLE (namesurname varchar(100));
INSERT @core_patient VALUES ('abc'),('aXxxx'),('bFggg'),('aLfred'),('andy');

SELECT c.namesurname
FROM @core_patient c
WHERE c.namesurname COLLATE Latin1_General_BIN LIKE 'a[A-Z]%';

    Returns:

    namesurname
--------------------
aXxxx
aLfred