asked on

MS SQL Server 2008 R2 - like 'S%' returns no rows but like 'Se%' does

I have a MS SQL 2008 R2 production database that does not like 'S%'

the statements:
select * from TLMS.ELEMENT where ELEMENT_ID like 'S%' ;
select * from TLMS.ELEMENT where ELEMENT_ID like 'S_' ;
select * from TLMS.ELEMENT where ELEMENT_ID like '[S]%' ;

all return zero rows.

Whereas the statement (for example)

select * from TLMS.ELEMENT where ELEMENT_ID like 'Se%' ;

returns two rows.

All other 'non-S-as-first-character' matches are OK and other databases on the same engine are OK with 'S%'.

Any ideas?

chaau

Is ELEMENT_ID varchar, nvarchar, or something else?

stephen_aus

ASKER

Thanks for your reply
varchar(12) - I have also just noticed that the Collation type that we have used on this database can cause unexpected results with pattern matching with wildcards.

The collation we are using is:
Latin1_General_100_CS_AI_KS

I think the solution is the latest service pack for MS SQL Server 2008 R2 or to change the collation type to something like: SQL_Latin1_General_CP1_CI_AS

chaau

Collation might be a problem.
You know, you can change the collation for one of the tables to test, before applying it to the whole database

stephen_aus

ASKER

The problem is that this is a production database with many PK, FK constraints - the change will be difficult. I will probably go with the latest service pack for SQL Server - thanks for you replies.

ASKER CERTIFIED SOLUTION

Tony303

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial