Finding a case sensitive record

Question

I have a requirement to fetch case sensitive records from only one column of a table.

i.e their is a column email_address from which i have to find the email address which are same but they are in different cases.

for eg:

email_ address
---------------
abc@gmail.com
hef@gmail.com
ABC@gmail.com
hrf@gmail.com

from the above example given.I need a query to fetch only the records abc@gmail.com and ABC@gmail.com from the column email_address.

What database platform are you using? SQL Server, MySQL..etc? Please tag the question appropriately. — crthompson, Jun 23 '14 at 03:45
This would Answer your Question - **[Link](http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm)** — Srivastav, Nov 25 '14 at 12:08

NeedAnswers · Answer 1 · 2014-06-23T04:15:38.630

Personally i don't like this approach, but it seems to be the only solution i have right now, and it works with mixed cases too :

select * from test t 
    where   
    email_address in 
    (
    select email_address from test group by email_address having count(email_address)> 1        
    )
    and left(email_address, charindex('@', email_address) -1 ) collate SQL_Latin1_General_CP1_CS_AS
        not in (select left(email_address, charindex('@', email_address) -1 ) from test 
                        where email_address collate SQL_Latin1_General_CP1_CS_AS <> t.email_address )

p/s : u need to replace the "test" with your actual table's name

Azar · Answer 2 · 2014-06-23T05:26:18.550

1

try this code

SELECT  *
FROM    table T 
JOIN    tALBE T2 
ON      T.EMAIL             =   T2.EMAIL
AND  CONVERT(varbinary,T.EMAIL)    <>       CONVERT(varbinary,T2.EMAIL)

edited Jun 23 '14 at 05:26

answered Jun 23 '14 at 04:15

Azar

1,852
15
17

It might help to explain your answer – geedubb Jun 23 '14 at 05:15
It works only when case is complete lower or upper for mixed it will not work. – Azar Jun 23 '14 at 05:32

score 1 · Answer 3 · answered Jun 23 '14 at 04:30

Assuming that your DB is not case sensitive, but you need to find values that are the same, but in a different case, this might work -

WITH CTE AS(
SELECT
email_address 
FROM TEST
GROUP BY email_address
Having count(1) > 1
)
SELECT TEST.email_address 
FROM TEST
INNER JOIN CTE ON
TEST.email_address=CTE.email_address

SQL FIDDLE

score 1 · Answer 4 · answered Jun 23 '14 at 06:49

1

Use Following condition

Where [email_ address] COLLATE Latin1_General_100_CI_AI = 'abc@gmail.com'

answered Jun 23 '14 at 06:49

mehdi lotfi

11,194
18
82
128

score 1 · Answer 5 · answered Jun 23 '14 at 07:07

1

Try this,

Select * from TableName where email_ address COLLATE Latin1_General_CS_AS = 'Your I/p Values'

answered Jun 23 '14 at 07:07

Vinoth_S

1,380
1
12
15

score 1 · Answer 6 · answered May 01 '15 at 13:40

1

Try this:

SELECT *
FROM
( VALUES ('abc'), ('ABC'), ('AbC')
) x(name)
WHERE name COLLATE Latin1_General_100_CS_AI = 'abc'

answered May 01 '15 at 13:40

Steve Ford

7,433
19
40

score 1 · Answer 7 · answered May 21 '15 at 08:00

Two ways we can Finding a case sensitive record 1.using COLLATE Latin1_General_CS_AI 2.using BINARY_CHECKSUM

create table EmailDetails(EID int identity(1,1),EmailAddress varchar(100))
insert into EmailDetails values('abc@gmail.com')
insert into EmailDetails values('ABC@gmail.com')
select EmailAddress from EmailDetails 
where EmailAddress COLLATE Latin1_General_CS_AI=LOWER(EmailAddress)
select EmailAddress from EmailDetails 
  where EmailAddress COLLATE Latin1_General_CS_AI='abc@gmail.com'
select EmailAddress from EmailDetails 
where BINARY_CHECKSUM(EmailAddress)=BINARY_CHECKSUM('abc@gmail.com')

score 0 · Answer 8 · edited May 23 '17 at 11:51

Here is answer,

declare @t table(email varchar(100))

insert into @t values('abc@gmail.com'),('hef@gmail.com'),('ABC@gmail.com'),('hrf@gmail.com')

declare @searchEmail varchar(100 )= 'ABC@gmail.com'

---1st approach 
SELECT *
FROM @t
WHERE   
    email = @searchEmail COLLATE SQL_Latin1_General_CP1_CS_AS

---2nd approach to convert in binary
select * from @t where CAST(email  as varbinary(100))  =  CAST(@searchEmail as varbinary(100))

Can be done via changing the Collation, by default it is case insensitive.

By using collation or casting to binary, like this:

Check the same answer ask here ..

How to do a case sensitive search in WHERE clause (I'm using SQL Server)?

SQL Server check case-sensitivity?

score 0 · Answer 9 · edited Jun 04 '15 at 04:36

0

Use A wildcard sql query operator or command.. I am not sure what it is called i forgot.. you use it by adding "%" sign both on left and right part of your WHERE variable like:

SELECT * FROM TABLE WHERE COLUMN = %@VARIABLE%

hope this helps

edited Jun 04 '15 at 04:36

Kurenai Kunai

1,842
2
12
22

answered Jun 02 '15 at 08:26

Alpha Gabriel V. Timbol

150
1
15

score -2 · Answer 10 · answered Jun 23 '14 at 03:56

JUST use "LIKE" in SQL :)

SELECT email_address FROM <tablename> WHERE email_address like 'abc@gmail.com'

it will display all email address ignoring the case :)

or you can use more advance like Begin with, Ends with, Contains With

SELECT email_address FROM <tablename> WHERE email_address like '%abc%' //contains with
SELECT email_address FROM <tablename> WHERE email_address like 'abc%' //Begin with
SELECT email_address FROM <tablename> WHERE email_address like '%abc' //Ends with

Finding a case sensitive record

10 Answers10