CODEWITHSUNDEEP
sqlregexpostgresql
spoko
spoko

Reputation: 803

PostgreSQL - ignoring sequence of characters in LIKE expression

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?

Upvotes: 1

Views: 538

Answers (2)

Linger
Linger

Reputation: 15058

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;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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.

Upvotes: 1

Related Questions