LIKE operator in SQL Server not working as expected

Question

I have the following SQL query:

select
    zz.teststring TEST_STRING
    ,case when zz.teststring like 'JA%' then 'true' else 'false' end [JA%]
    ,case when zz.teststring like 'J%' then 'true' else 'false' end [J%]
    ,case when zz.teststring like 'JAA%' then 'true' else 'false' end [JAA%]
from 
    (select 'jaa' teststring) zz

which outputs

TEST_STRING      JA%       J%       JAA%
jaa              false     true     true

Upper/lower case makes no difference.

I'd really appreciate it if anyone could explain why the JA% returns false?

Answer 1

You need to check collation probably is set to Case Sensitive:

Demo

select
zz.teststring TEST_STRING
,case when zz.teststring like 'JA%' then 'true' else 'false' end [JA%]
,case when zz.teststring like 'J%' then 'true' else 'false' end [J%]
,case when zz.teststring like 'JAA%' then 'true' else 'false' end [JAA%]
from (
    select 'jaa' teststring
) zz

For Case Insensitive add COLLATE Latin1_General_CI_AS

select
zz.teststring TEST_STRING
,case when zz.teststring like 'JA%'  COLLATE Latin1_General_CI_AS then 'true' else 'false' end [JA%]
,case when zz.teststring like 'J%' COLLATE Latin1_General_CI_AS then 'true' else 'false' end [J%]
,case when zz.teststring like 'JAA%' COLLATE Latin1_General_CI_AS then 'true' else 'false' end [JAA%]
from (
    select 'jaa' teststring
) zz

EDIT:

But I wonder how you get your answer false, true, true?:

Demo2

select
zz.teststring TEST_STRING
,case when zz.teststring like 'JA%' then 'true' else 'false' end [JA%]
,case when zz.teststring like 'J%' then 'true' else 'false' end [J%]
,case when zz.teststring like 'JAA%' then 'true' else 'false' end [JAA%]
from (
    select 'jaa' COLLATE Latin1_General_CS_AS teststring
) zz

Thanks jarlh mystery is solved:

COLLATE Danish_Norwegian_CI_AI

Demo3

With some languages 'aa' is treated as one character.