Forum Stats

  • 3,734,276 Users
  • 2,246,935 Discussions
  • 7,857,216 Comments

Discussions

LIKE operator in CHAR fields with leading spaces in Oracle 12cR2

User_OX9Q3
User_OX9Q3 Member Posts: 22 Green Ribbon
edited Nov 11, 2020 3:16PM in SQL & PL/SQL

I am working with Oracle 12cR2 and with SQL Server 2014, performing some tests I found certain differences with the LIKE operator in both managers, the queries that give me different results are the following, the fields are of type char with a length of 40, in Oracle the collection of characters that I use at the column level is BINARY, NLS_SORT = BINARY_CI, NLS_COMP = LINGUISTIC and NLS_CHARACTERSET = WE8MSWIN1252, in SQL Server at the database level the character collection is Latin1_General_CI_AS and at the column level is Latin1_General_B:


SQL Server:

SELECT NAME FROM PEOPLE WHERE NAME LIKE 'JOSE'; Result: JOSE

SELECT NAME FROM PEOPLE WHERE NAME LIKE '%OSE'; Result: JOSE


Oracle:

SELECT NAME FROM PEOPLE WHERE NAME LIKE 'JOSE'; Result: No row has been selected

SELECT NAME FROM PEOPLE WHERE NAME LIKE '%OSE'; Result: No row has been selected

My question is, is there any configuration that needs to be done in Oracle to prevent it from taking the spaces to the right into account? Beforehand thank you very much

Answers

  • Paulzip
    Paulzip Member Posts: 8,322 Blue Diamond

    No idea why people use char(..), it's an abomination to me, has weird behaviour and takes up unnecessary space too. My advice, use varchar2, then you wouldn't have this problem.

    I believe Oracle is correct here and SQL Server isn't. How would the DB know if you meant the terminating spaces or not? I don't see how it could, so it should include them in the LIKE evaluation.

    User_OX9Q3
  • mathguy
    mathguy Member Posts: 9,779 Gold Crown

    "Configuration"? No. There is no setting in Oracle to force LIKE to behave as it does in SQL Server. So, you need a workaround.

    A simple one that comes to mind, which uses only an additional standard string function (so it won't hurt execution speed too much) and should work exactly the same in Oracle and in SQL Server, is to do something like this:

    ... where rtrim(name) like '%ose'
    

    In both SQL dialects, RTRIM() trims spaces from the right end of its argument. Apparently the LIKE comparison in SQL Server does something like this behind the scenes; making it explicit, then, may not hurt performance at all, while making the intent clearer.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,398 Black Diamond

    And why would SQL Server ignore spaces in LIKE operator?

    I:\>sqlcmd -S XXX
    1> with people(name) as (select ' JOSE')
    2> select name from people where name like '% OSE'
    3> go
    name
    ----
    
    (0 rows affected)
    1> with people(name) as (select ' OSE')
    2> select name from people where name like '% OSE'
    3> go
    name
    ----
     OSE
    
    (1 rows affected)
    1>
    

    SY.

  • mathguy
    mathguy Member Posts: 9,779 Gold Crown

    I saw the space between the percent sign and OSE too; I should have mentioned it. I assume that is a typo and the OP really meant %OSE (or else, he is lying about the result in SQL Server, and I see no reason why he would).

    The question is obviously about TRAILING spaces, not that errant space in '% OSE'. And, indeed, according to the documentation (the only thing I can lean on, since I don't know anything about SQL Server), when comparing CHAR(n) values with the LIKE operator, SQL Server does disregard trailing spaces, unlike Oracle.

    To test that (to test exactly what the OP reported, minus the typo) you would need to convert the literal string 'JOSE' in your WITH clause to CHAR(40) - I don't suppose that's the default for string literals in SQL Server. I don't see an attempt to do that in your test.

  • User_OX9Q3
    User_OX9Q3 Member Posts: 22 Green Ribbon

    The space between '% ose' was my mistake when writing the question, I have already corrected it and the proposal I opted for was to change the data type to VARCHAR2

Sign In or Register to comment.