CODEWITHSUNDEEP
mysql
John
John

Reputation: 103

How to concat 2 fields in query

I have a native query: 

@Query("SELECT p FROM Person p WHERE (" +
            "(lower(p.name) like concat('%',:source,'%'))" +
            "OR (lower(p.surname) like concat('%',:source,'%'))" +
            "OR (lower(p.email) like concat('%',:source,'%'))" +
            "OR (lower(p.phone) like concat('%',:source,'%')))")
    Page<Person> search(@Param("source") String source, Pageable pageable);

And it allows me to search for name/surname etc. Although I would like to search for name AND surname in browser as well (with space).

I tried adding 

"(lower(p.name) like concat('%',:source,'%') AND (lower(p.surname) like concat('%',:source,'%')))" +
            "OR (lower(p.name) like concat('%',:source,'%'))" +

etc...

But it is not working. How can I do this?

Upvotes: 0

Views: 54

Answers (2)

Chronocidal
Chronocidal

Reputation: 51

I'm trying to work out if you have the Concat in the right place - for example, if Source='Joe Bloggs', and you are looking for p.name='Joe' and p.Surname='Bloggs', then don't you want(concat(p.name, ' ', p.surname') LIKE concat('%', :source:, '%'))to evaluate as'Joe Bloggs' LIKE '%Joe Bloggs%'

Or am I getting confused?

Upvotes: 0

RichGoldMD
RichGoldMD

Reputation: 1282

Try something like

concat(p.name, ' ', p.surname) like concat('%', :source, '‰')

Which will only work if both are pretty close and the space matches exactly. You may also be able to find a way to use rlike with word boundaries.

Upvotes: 1

Related Questions