PostgreSQL+NHibernate -> named parameter in LIKE section

Question

In Spring+Hibernate app im trying to use a named parameter pasend query should sum values from xml document saved in database as text.

    SELECT  document_type, 
     SUM(CAST(substring(document_content ,'<ab.*>(.*[0-9])</ab>') as float)) as value, COUNT(*)
     FROM  statistic_data_test 
     WHERE column LIKE :param1::text
     GROUP BY document_type 
     ORDER BY value DESC

param1 is named parameter passed to program via URL. Whole query and parameter names are dynamically created by user.

code shown above works fine, but i need an ability to find not only exact match, but i cant concatenate stings in query. For now, in one part of code i'm checking if type is text, and if so, i add % marks as suffix and prefix.

What i wont, is move % marks from parameter to query string, but things like

WHERE column LIKE '%'+:param1::text+'%'

turns errors

How can it be done? How to concatenate stings in query?

Jirka Hanika · Accepted Answer · 2012-07-18 20:36:29Z

1

Change the strategy, keep the goal.

Use the position function.

position(:param1::text in column) <> o

answered Jul 18, 2012 at 20:36

Jirka Hanika

13.7k3 gold badges52 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

T.G Over a year ago

Thanks, it looks what i was looking for ;) But is there a way to check if nothing else except that param is in column? Cant be done by LIKE ofc, but would be good to use one method.

Jirka Hanika Over a year ago

@T.G - Well, LIKE can do that simply by including no percent signs, but you would suffer a major performance hit on any larger data if you did not just use = for that job. Hammer for the nails, screwdriver for the screws.

Collectives™ on Stack Overflow

PostgreSQL+NHibernate -> named parameter in LIKE section

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related