Search code examples
sqljdbcjavadb

SQL LIKE operator and trailing spaces

I am new to SQL and trying to make sense of this problem. My book says I should be able to run and obtain a result from the following:

SELECT * FROM Coffee WHERE ProdNum LIKE '2_-00_'

When I run this I do not get any results. I am expecting a results like 21-001 or 24-003. I have tried these alternatives which work:

SELECT * FROM Coffee WHERE ProdNum LIKE '2_-00%'

Or

SELECT * FROM Coffee WHERE ProdNum LIKE '2_-00_    '

The ProdNum column is initialized with 10 Characters which is why the last one works. From what I read, I am under the impression that LIKE would ignore trialing blank spaces. Is this correct?

Solution

  • There's two common types of character fields:

    1. CHAR - This will have exactly the length defined (so in the case of 10 character field, it'll always be 10 characters and have spaces at the end if the string is less than 10).

    2. VARCHAR - The data in this field will have a length less than or equal to 10 characters. The data is not padded with spaces.

    Let's take the CHAR example:

    SELECT * FROM Coffee WHERE ProdNum LIKE '2_-00_'

    The data 24-001 has 4 training spaces, so ProdNum LIKE '2_-00_' will return false for that record and thus the record will not be returned.

    Now, VARCHAR

    SELECT * FROM Coffee WHERE ProdNum LIKE '2_-00_'

    The data 24-001 will not have the trailing spaces. so ProdNum LIKE '2_-00_' will return TRUE for that record and thus the record WILL BE returned.