Solved Excel Add-in SQL Compare two columns for match in string regardless of case

Posted on 2014-08-05
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:murbro
    LVL 14

    Accepted Solution


    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
    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.
    LVL 7

    Expert Comment

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

    Author Closing Comment

    Thanks very much

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Suggested Solutions

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    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.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    758 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now