Q
Problem solve Get help with specific problems with your technologies, process and projects.

Find a partial string in a field that contains a long string

SQL Server expert Jeremy Kadlec explains how to use LIKE to find a partial string in a field.

I am trying to find a partial string in a field that contains a big long string. For example, SELECT a.* WHERE (a.Status LIKE "%|x|%") The above example works great, except for one thing, the "x" is not case sensitive and that is an issue. Any quick ideas?
I would recommend testing the following query to capture all of the authors with a last name starting with 'K' in the Pubs database:
SELECT a.* 
FROM Authors a
WHERE a.au_lname LIKE '[K_]%'
GO

For more information about LIKE I would recommend the following article:

LIKE

As far as the case sensitivity is concerned, I think your issue is based on the character set and sort order for SQL Server. To determine the character set and sort order issue the following command:

sp_helpsort

For more information about sp_helpsort I would recommend the following article:

sp_helpsort

I would assume your SQL Server is configured with a case insensitive character set and sort order where a lower case letter equals an upper case letter i.e. 'k' = 'K'. I believe this installation option is preventing you from being able to complete your query as you requested.


Do you have comments on this Ask the Expert Q&A? Let us know.

Dig Deeper on SQL-Transact SQL (T-SQL)

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close