Search for case sensitive entries

Question

I have a requirement for getting case sensitive entries from only one column of a table.

ie them is the email_address column, from which I have to find the email address, which is the same, but they are in different cases.

eg:

email_ address
---------------
[e-mail]
[e-mail]
[e-mail]
[e-mail]

from the above example. I need a query to retrieve only records [e-mail] [e-mail] a column email_address.

Answer 1

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

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 "test" with your actual table name

Answer 2

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

Answer 3

, , , -

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

Answer 4

Where [email_ address] COLLATE Latin1_General_100_CI_AI = '[e-mail]'

Answer 5

,

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

Answer 6

:

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

Answer 7

1. COLLATE Latin1_General_CS_AI 2. BINARY_CHECKSUM

create table EmailDetails(EID int identity(1,1),EmailAddress varchar(100))
insert into EmailDetails values('[e-mail]')
insert into EmailDetails values('[e-mail]')
select EmailAddress from EmailDetails 
where EmailAddress COLLATE Latin1_General_CS_AI=LOWER(EmailAddress)
select EmailAddress from EmailDetails 
  where EmailAddress COLLATE Latin1_General_CS_AI='[e-mail]'
select EmailAddress from EmailDetails 
where BINARY_CHECKSUM(EmailAddress)=BINARY_CHECKSUM('[e-mail]')

Answer 8

,

declare @t table(email varchar(100))

insert into @t values('[e-mail]'),('[e-mail]'),('[e-mail]'),('[e-mail]')

declare @searchEmail varchar(100 )= '[e-mail]'

---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))  

Collation, .

, :

, , .

WHERE ( SQL Server)?

SQL Server -?

Answer 9

sql-. , , .. , "%" , WHERE:

SELECT * FROM TABLE WHERE COLUMN = %@VARIABLE% 

,

Answer 10

"LIKE" SQL:)

SELECT email_address FROM <tablename> WHERE email_address like '[e-mail]'

, :)

, 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