Search code examples
sql-serverselecttextlowercase

SQL search for values in a field with text datatype regardless of casing

I am trying to get all values for a particular search regardless of casing. On our SQL Server database case sensitivity is turned on and I don't want to have to change this if possible.

If I do a SELECT statement that includes the LOWER() function as follows

SELECT COUNT(blogpostId) as blogpostcount
FROM blogposts
WHERE stateId = '1'
AND blogId = '20'
AND LOWER(blogpostContent) LIKE '%test%'

it throws and error to say

Argument data type text is invalid for argument 1 of lower function.

The data type for the blogpostContent column is text. If I change this to nvarchar this works however nvarchar only allows a maximum of 255 chars and I need a lot more than this.

Is there anyway to check for results in the text field regardless of casing??

Thanks in advance

Solution

  • You could explicity force it to use a CASE INSENSITIVE collation like so: 

    SELECT COUNT(blogpostId) as blogpostcount  
FROM blogposts  
WHERE stateId='1'  
AND blogId = '20'  
AND blogpostContent LIKE '%test%'  COLLATE SQL_Latin1_General_CP1_CI_AS