CODEWITHSUNDEEP
sqlsql-servercomparevarcharcase-insensitive
Sara N
Sara N

Reputation: 1189

Compare two charcolumns without case sensivity in SQL server

I have an SQL select which have a join in a part on a column which is AAA in one table and aaA in another one. I want to join without regarding to case .but it doesnt return right answer(I want all records to be returned during this select)

SELECT *
FROM
   TABLE1 
   INNER JOIN TABLE2
      ON lower(TABLE1.Col1) = lower(TABLE2.Col2);

I added COLLATE Latin1_General_CS_AS after select but no correct answer. I set the collation of both columns to deafult database collation , but no answer.

help please

Upvotes: 1

Views: 52

Answers (2)

ErikE
ErikE

Reputation: 50201

If the code you're showing doesn't return the rows you're expecting, then the problem is something besides letter case. There are either unprintable or other characters in the strings, or you're looking at the wrong tables, or some other issue. Using lower or COLLATE with a case-insensitive collation (containing "CI") will both work. If they're not working, it's something else.

Upvotes: 1

dito003
dito003

Reputation: 66

I think you want to use COLLATE SQL_Latin1_General_CP1_CI_AS. The CI is CaseIncensative.

select *
from t1
join t2 ON t1.Col1 COLLATE SQL_Latin1_General_CP1_CI_AS = t2.Col2 COLLATE SQL_Latin1_General_CP1_CI_AS

Upvotes: 1

Related Questions