Find ONLY Capital Letters in word through IN SQL Server query

Question

I have to do this in SQL Server

I have data such as

Belo Horizonte , MG - Brazil
São Paulo , SP - Brazil
Barueri , SP - Brazil
Ferraz de Vasconcelos , SP - Brazil

I need to select two letter word as that matches the pattern

Space Letter Letter

I have tried this

SUBSTRING(ADDRESS_BLOCK,PatIndex('% [A-Z][A-Z] %',ADDRESS_BLOCK),3)

But I need to consider only capital letters for this (i.e) output has to be

MG SP SP SP 

And not include de as found in the last line of the example Ferraz de Vasconcelos , SP - Brazil

Clear view on the problem

Eg: vaishnava st northwind GH -- Result has to be GH

 somersert PM vailash hj  --Result has to be PM

Answer 1

Try this: You need to both collate the column AND specify the capital letters. The regular expression [A-Z] is not case sensitive, even if you specify a collation sequence.

SELECT    SUBSTRING(
            ADDRESS_BLOCK
            , PatIndex(    
                N'% [ABCDEFGHIJKLMNOPQRSTUVWXYZ][ABCDEFGHIJKLMNOPQRSTUVWXYZ] %'
                , ADDRESS_BLOCK COLLATE sql_latin1_general_cp1_cs_as
                )
            , 3
            ) 
FROM 
    (
        SELECT 'Belo Horizonte , MG - Brazil' ADDRESS_BLOCK
        UNION
        SELECT 'São Paulo , SP - Brazil' 
        UNION
        SELECT 'Barueri , SP - Brazil' 
        UNION
        SELECT 'Ferraz de Vasconcelos , SP - Brazil' 
    ) n

Answer 2

I think this will do it... or a variation thereof to suit your needs. In this case it'll pick out the first pair of of uppercase letters.

with dataset as 
(
    select 'Belo Horizonte , MG - Brazil' as val union all
    select 'São Paulo , SP - Brazil' as val union all 
    select 'Ferraz de Vasconcelos , SP - Brazil'  
)
select Substring(val ,PatIndex('%[A-Z][A-Z] %' COLLATE LATIN1_gENERAL_BIN,val),3)
from dataset