CODEWITHSUNDEEP
javasqlpostgresqlspring-jdbc
Alex
Alex

Reputation: 2030

Spring JDBC Postgresql where in like

I have following query:

SELECT *
FROM user_info
WHERE phone_number IN (:phoneNumbers)

I want to search not by exact phone number but using LIKE for each one. How I can implement that?

Upvotes: 0

Views: 1248

Answers (4)

Vao Tsun
Vao Tsun

Reputation: 51406

you can do some monstrous workaround, like:

t=# with a as (values('some','postg','temp'))
, p as (select unnest(string_to_array(translate((a.*)::text,'()',''), ',')) patt from a)
select datname,concat(patt,'%') from p left outer join pg_database on datname like concat(patt,'%');
  datname  | concat
-----------+--------
           | some%
 postgres  | postg%
 template1 | temp%
 template0 | temp%
(4 rows)

here I use outer join to demonstrate how it works (some% does not match any dbname and thus datname is null). List 'some','postg','temp' should be put to values, not to IN scalar list. Also I know my dtabases do not have () in names , thus use translate here, instead of cutting first and last parenthesis.

Upvotes: 0

user330315
user330315

Reputation:

You can pass an array:

SELECT *
FROM user_info
WHERE phone_number like any ( array['123%', '456%'] );

Not sure how you pass an array from within Spring JDBC Template though

Upvotes: 0

shimatai
shimatai

Reputation: 1808

You have to use LIKE statement:

SELECT * FROM user_info WHERE phone_number LIKE '123%'

This query will match all phonenumbers starting with 123. If you need to match a phonenumber which contains 123, you should use:

SELECT * FROM user_info WHERE phone_number LIKE '%123%'

Now it'll match ANY phonenumber which contains 123. In PostgreSQL you don't need to cast a number to string if using LIKE (PostgreSQL does it for you automagically).

Upvotes: 1

Gal Shaboodi
Gal Shaboodi

Reputation: 764

You can use PostgreSql SIMILAR TO with regex.

Upvotes: 0

Related Questions