CODEWITHSUNDEEP
sqlpostgresqlpaddingsql-likesqldatatypes
paolov
paolov

Reputation: 2327

Using underscore in Postgresql LIKE

I am trying to use underscores in a postgresql LIKE to perform a search for particular pattern of data but it does not seem to work.

The client is version 9.2.21 and the server is on version 9.4.12.

For example, 

select * 
from table1 
where f1 like '___'

returns nothing even when there is clearly a match in the table--i.e, there is data with a value that matches--e.g. f1 = '123'.

I am wondering if there is a system config / parameter that influences this behaviour?

Or can this be explained some other way?

Upvotes: 0

Views: 715

Answers (1)

user330315
user330315

Reputation:

Values stored in columns defined as char(9) will be padded with blanks to the defined length. So when you store '123' in that column, it actually stores '123 '

You need trim those trailing blanks:

where trim(f1) = '___'

But you should avoid the char type completely. It offers no advantage whatsoever over varchar but has several disadvantages (you just found one)

Upvotes: 6

Related Questions