How to write a hql query to search one word from comma separated words

Question

I am trying to write a hql query to search one word from comma separated words.

My input is like abc,xyz,par.

Expected result: Should be displayed the matched record if any of the words matches the REGEX.

I tried with REGEXP_LIKE regular expression (Ex: REGEXP_LIKE(columnname, "value")) in hql but it is not working.

Please suggest me an hql query for given scenario.

UPDATE: My attempt is:

SELECT emp.title 
FROM employee as emp 
WHERE 1=1 and (emp.title regexp_like (emp.title, :empTitle))

Thanks in advance.

SELECT emp.title FROM employee as emp WHERE 1=1 and (emp.title regexp_like (emp.title, :empTitle)). here empTitle is comma separated values. If it matches with one or more values the it should display the matched records. — Subbu Subramanyam, Jul 05 '16 at 13:16
possible duplicate of http://stackoverflow.com/questions/5611715/where-value-in-column-containing-comma-delimited-values — udaybhaskar, Jul 05 '16 at 13:17
@udaybhaskar, I think it is not duplicate because I am asking how to write query on hql. Please provide me any suggestions. — Subbu Subramanyam, Jul 05 '16 at 13:19
He is using HQL and he may be motivated to not use a database-specific regex function. — Tim Biegeleisen, Jul 05 '16 at 13:28

score 0 · Answer 1 · answered Jul 05 '16 at 13:13

0

I believe you can get away with just using LIKE and eschewing more complex regex methods. Try using this WHERE clause:

WHERE columnname LIKE 'value,' OR
      columnname LIKE ',value,' OR
      columnname LIKE ',value'

answered Jul 05 '16 at 13:13

Tim Biegeleisen

Thanks for suggestion. There may be multiple values (Keywords search) – Subbu Subramanyam Jul 05 '16 at 13:24

1 Answers1