CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008
John Smith
John Smith

Reputation: 187

Select rows that contain both English and non-English characters

Ok so I have a column in a table in SQL Server. Each records have a string (i.e. names) SOME of the strings have English AND NON ENGLISH characters.

I have to select ONLY the records that have English AND NON English characters.

How do I go about doing that?

My try...

Select * from Table
Where Nameofthecolumn NOT LIKE '%[A-Z]%'
Go

This will give me EMPTY table.

I know for sure that there are at least two records that have english and non-english characters.

I need those two records as output.

I was trying to do 

Select * from Table
Where Nameofthecolumn NOT LIKE '%[A-Z,a-z]%' AND Like '%[A-Z,a-z]%'
Go

but turns out you can use boolean with Like/Not Like.

Please guide me the right direction.

Thanks

Upvotes: 2

Views: 6739

Answers (2)

Dibstar
Dibstar

Reputation: 2364

Do you mean something like this?

select 1 as field1,'1' as field2 into #data
union all select 1,'abc'
union all select 2,'abc'
union all select 3,'999'


SELECT * FROM 
(
select field1,field2
,MAX(CASE WHEN field2 NOT LIKE '%[A-Z,a-z]%' THEN 1 ELSE 0 END) OVER (PARTITION BY field1) 
 + MAX(CASE WHEN field2 LIKE '%[A-Z,a-z]%' THEN 1 ELSE 0 END) OVER (PARTITION BY field1) as chars 
FROM #data
) alias
WHERE chars =2

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280351

How about reversing your search, e.g. find anything that doesn't match A-Z:

... WHERE col LIKE '%[^A-Z]%' AND col LIKE '%[A-Z]%';

If the collation is case insensitive you shouldn't need a-z, if it is case sensitive you could add the COLLATE clause. But you may want to filter out spaces, numbers and other non-alphanumerics that are expected.

Upvotes: 1

Related Questions