Geek asks and answers

How to find ONLY WRITTEN letters in a word through a SQL Server query

I need to do this on 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 words that match the pattern

Space Letter Letter

I tried this

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

But I need to consider only capital letters for this (ie) output should be

MG SP SP SP

And do not include de, as shown in the last line of the exampleFerraz de Vasconcelos , SP - Brazil

Clear problem view

For example: vaishnava st northwind GH - The result should be GH

 somersert PM vailash hj  --Result has to be PM
+5
source share
5 answers

Try the following: you need to match the column AND specify the capital letters. The regular expression [AZ] is not case sensitive, even if you specify a sort 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
+3
source

2 :

select substring(columnname, charindex(',', columnname) + 2, 2)
from tablename

-:

select substring(columnname, charindex('-', columnname) - 3, 2)
from tablename

, , :

SUBSTRING(ADDRESS_BLOCK,PatIndex('% [ABCDEFGHIJKLMOPQWXYZ][ABCDEFGHIJKLMOPQWXYZ] %',ADDRESS_BLOCK),3)

.

0

:

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

0

, ... ., ...

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

Aamer

0

. Collate , :

Select * from table where exists (Select SUBSTRING(ADDRESS_BLOCK,PatIndex('% [A-Z][A-Z] %',ADDRESS_BLOCK),3) from table)
COLLATE Latin1_General_CS_AS_KS_WS ASC;

- . Latin1_General_CS_AS_KS_WS , , CI CS, senstive .

: http://msdn.microsoft.com/en-us/library/ms184391.aspx

-1

More articles:


All Articles