1

I wonder if there is a way to ignore a sequence of characters in PostgreSQL expression LIKE.
In my table I have some values, which start with varying number of zeros. I want to be able to find proper values, without putting those zeros in my LIKE expression. There is an example:

id value
1  00045
2  00145
3  00003

Now, when I start looking for value 00045, I want to type just "45" and have only value connected with id=1 returned. LIKE '%45' is obviously not possible, as I don't want value 00145 picked.

Is there a simple way to do so?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
spoko
  • 783
  • 1
  • 10
  • 24

2 Answers2

1

You can cast the field as an integer in your where clause before you compare (SQL Fiddle):

SELECT * 
FROM MyTable m
WHERE CAST(coalesce(m.value, '0') AS integer) = 45

If you know value will always contain something then you can leave out the coalesce (SQL Fiddle):

SELECT * 
FROM MyTable m
WHERE CAST(m.value AS integer) = 45;
Linger
  • 14,942
  • 23
  • 52
  • 79
  • Thanks for the answer, but I don't want to cast the values to integers. I want them to stay as character varying. – spoko Aug 04 '14 at 14:35
  • **@spoko**, they will stay as ***characters varying***. I am only casting them in the where clause so you can compare and get what you want. In the select statement I just specify `*` which means return all the fields as they are. So the `m.value` will be returned as varchar. – Linger Aug 04 '14 at 16:19
  • you're right, my bad. Casting to integer will not allow me to use `LIKE '45%'` though, but your comment made me wonder what would happen if I casted the expression to TEXT like in this example: http://stackoverflow.com/a/1684312/3739215. I'm gonna give it a try. – spoko Aug 04 '14 at 20:08
1

You can use the ltrim() function. It takes a second argument of leading characters to omit:

select *
from example e
where ltrim(e.value, '0') = '45';

Alternatively, if the values are always 5 characters long, you can use:

select *
from example e
where e.value = lpad('45', 5, '0');

The advantage of the first approach is that it does not assume that the value is an integer. The advantage of the second is that it allows Postgres to use an index on example(value), if one is available.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This (first solution) looks promising; however, when I use it like `where ltrim(e.value, '0') ilike` I get syntax error near '0'. Any ideas? – spoko Jul 30 '14 at 07:57
  • @spoko . . . The example in your comment is not related to recommendations in my answer. – Gordon Linoff Jul 30 '14 at 12:35
  • Sorry if this sounds dumb, but could you expand your thought? I mean, why using `like` instead of `=` makes my example unrelated? I have text type anyway; however `=` does not give me the privilege of using `%`. – spoko Jul 30 '14 at 20:36
  • Ok, I worked this thing out. Your solution and my example were pretty related, but my colleague, who wrote the part of the code I was supposed to change was building queries by adding strings and every one of them was started and closed by '. Adding additional pair of ' did the trick. Nonetheless `ltrim` was the function I eventually used, so I mark this post as a solution. – spoko Aug 05 '14 at 13:57