通过 IN SQL Server 查询在 word 中仅查找大写字母 [英] Find ONLY Capital Letters in word through IN SQL Server query

问题描述

我必须在 SQL Server 中执行此操作

I have to do this in SQL Server

我有这样的数据

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

我已经试过了

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 

并且不包括示例最后一行中的 de Ferraz de Vasconcelos , SP - Brazil

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

清晰地看清问题

例如:vaishnava st northwind GH -- 结果必须是 GH

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

 somersert PM vailash hj  --Result has to be PM

推荐答案

试试这个:您需要整理列并指定大写字母.正则表达式 [A-Z] 不区分大小写，即使您指定了整理顺序.

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

这篇关于通过 IN SQL Server 查询在 word 中仅查找大写字母的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！