Using a column field in SQL like or regexp operator

Question

I have a query:

Select * 
from table 
where 
field1 LIKE '%12345%' (12345 being a column value : field2) *Works*

I want to use a column field value in the LIKE operator instead of hardcoding. Tried using concat:

where field1 LIKE concat(concat('%',field2), '%') *doesnt work*

Tried using regexp_like:

where regexp_like(field1, Cast(field2 as character)) *doesnt work*

What is "field2"? A column name or what? How do you define "field2"? — mauro, Mar 17 '16 at 16:58

ScaisEdge · Answer 1 · 2016-03-17T07:47:23.787

0

Use just one concat

where field1 LIKE concat( '%',TRIM(field2), '%')

edited Mar 17 '16 at 07:47

answered Mar 17 '16 at 07:32

ScaisEdge

131,976
10
91
107

I have tested the code before answer . have you error ? . if don't work could be dont match (but work) .. – ScaisEdge Mar 17 '16 at 07:38
I have update the answer with a trim for clean the field ... try – ScaisEdge Mar 17 '16 at 07:48
1

@Neha - just as an aside "doesn't work" *isn't* useful feedback to give people. Be *specific*. Is it returning no results, wrong results, an error message, etc? Those are all different ways in which a suggested answer may not work and knowing the specifics (e.g. if it's wrong results, how are they wrong/ If it's an error message, *what* is the error) can be immensely helpful in guiding people towards *helping* you, especially when they don't have access to your data nor know what your aim is (hint: adding sample data and expected results to the question would also help) – Damien_The_Unbeliever Mar 17 '16 at 09:52

score 0 · Answer 2 · edited Apr 10 '16 at 15:31

0

See below:

dbadmin=> create table test1 (a varchar(100));

CREATE TABLE
dbadmin=> 

insert into test1 values('Good morining ');
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from test1 where a like '%'||a||'%';
       a
----------------
 Good morining
(1 row)

edited Apr 10 '16 at 15:31

Maxim

9,701
5
60
108

answered Mar 17 '16 at 09:07

elirevach

404
2
7

Using a column field in SQL like or regexp operator

2 Answers2