VB.net Excel Add-in SQL Compare two columns for match in string regardless of case

Posted on 2014-08-05
Medium Priority
Last Modified: 2014-08-05

I have two columns in separate table that contain names. My problem is that the cases differ, for instance the name
"John Cooper" in the one column ad in the second table it may appear as "JOHN COOPER". What SQL code would I use to ignore case sensitivity. I am currently using the following code:

    sSQL = "Select Distinct [LINK ID] From [BANK] Where [DESCRIPTION] = '" & oBankDescription & "'

Question by:Murray Brown
LVL 15

Accepted Solution

Vikas Garg earned 1000 total points
ID: 40240733

You can try many things but the simplest would be to convert both in same case

"Select Distinct [LINK ID] From [BANK] Where lower([DESCRIPTION]) =  Convert to lower '" & oBankDescription & "'

By doing conversion both side you can get your results.

Other thing you can change the Collation of SQL to

example SQL_Latin1_General_CP1_CI_AS - In this CI stands for Case Insensitive which is by default
LVL 25

Assisted Solution

by:Luis Pérez
Luis Pérez earned 1000 total points
ID: 40240734
You can convert both strings to upper case or lower case, so you can use:

 sSQL = "Select Distinct [LINK ID] From [BANK] Where UPPER([DESCRIPTION]) = '" & oBankDescription.ToUpper() & "'

That will match all the cases.

Hope that helps.

Expert Comment

by:Pushpakumara Mahagamage
ID: 40240793
you can set a collation for a column, What is the collation for your Database?

Author Closing Comment

by:Murray Brown
ID: 40241440
Thanks very much

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question