Search code examples
sqlsql-servercharindex

Find string in column value SQL 2008?

I have used coldfusion Find() function to check if value contains M (upper case) letter. Instead of coldfusion I would like to do this in SQL query. There is few solutions that I found but I'm not sure if that would take care of upper case. Also I'm not sure what is the best function to use since I have around 80 columns and I don't want to slow down query. Here is what I have so far:

CASE
    WHEN CHARINDEX ( 'M', column1) > 0 THEN 'T'
        ELSE 'F'
    END AS column1

If anyone knows more about this function or some better solution please let me know. Thank you.

Solution

  • SQL Server by default is not case-sensitive. If you want to do a case-sensitive search, you're going to need to make use of the case-sensitive collation.

    Collation to be used for case-sensitivity: COLLATE Latin1_General_CS_AS

    Here's your query, adjusted to a case-sensitive search: 

    CASE
    WHEN CHARINDEX ( 'M', column1 COLLATE Latin1_General_CS_AS) > 0 THEN 'T'
        ELSE 'F'
    END AS column1