CODEWITHSUNDEEP
sql-server
nona123meeee
nona123meeee

Reputation: 41

Sql command - like with % operator

In table i have row where in column 'name' is 'name123' First sql command return this row but second command do nothing , why ?

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

select * from Osoby where imie like '%123'

Upvotes: 4

Views: 1304

Answers (5)

Luka Milani
Luka Milani

Reputation: 1541

to prevent spaces problems try this:

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

Upvotes: 1

Sai
Sai

Reputation: 3967

In line with what others are suggesting, try this --

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

and verify that you are getting the row

Upvotes: 2

Pleun
Pleun

Reputation: 8920

Check your datatypes and have a look at: http://msdn.microsoft.com/en-us/library/ms179859.aspx

Pattern Matching by Using LIKE

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

Upvotes: 1

Rex Morgan
Rex Morgan

Reputation: 3029

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

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

In essence, the query you posted should work, it sounds like something's wrong with your data.

Upvotes: 1

Philip Sheard
Philip Sheard

Reputation: 5825

Perhaps the field has trailing spaces.

Upvotes: 1

Related Questions