Sql command - as for the% operator

Question

In the table, I have a row where the column 'name' says 'name123' The first sql command returns this row, but the second command does nothing, why?

select * from Osoby where imie like '%123%' select * from Osoby where imie like '%123' 

Answer 1

As others suggest, try this -

 select * from Osoby where RTRIM(LTRIM((imie)) like '%123' 

and make sure you get the string

Answer 2

The field may have trailing spaces.

Answer 3

If the imie field is a char field, it will fill in everything you put into it, with spaces to reach the field length. If you change this to the varchar field, you can get rid of trailing spaces.

If you change your field to varchar, run UPDATE Osoby SET imie = RTRIM(imie) to trim extra spaces.

In fact, the request that you posted should work, it sounds somehow wrong with your data.

Answer 4

Check your data types and see: http://msdn.microsoft.com/en-us/library/ms179859.aspx

Matching patterns with LIKE

LIKE supports ASCII pattern matching and Unicode pattern matching. When all arguments (match_expression, pattern, and escape_character, if any) are ASCII character data types, ASCII pattern matching is performed. If either argument is a Unicode data type, all arguments are converted to Unicode and the Unicode pattern matches. When you use Unicode data (nchar or nvarchar data types) with LIKE, trailing spaces are significant; however, for non-Unicode data, trailing spaces are not significant. Unicode LIKE is ISO compliant. ASCII LIKE is compatible with earlier versions of SQL Server.

Answer 5

to prevent problems with spaces try this:

 select * from Osoby where ltrim(rtrim(imie)) like '%123'