To Search a String in a different Column String , in which Search String is just part of - SQL Question

Database

To Search a String in a different Column String , in which Search String is just part of - SQL Question

Hello Everyone  , Need Help !!

I wanted to search a string which is matched from a different coulumn , for eg

ABCD is to be searched in Column of different table column , value in that column can be like '1234abcd789ABCD' , basically need to search a entire string in different column which can hold the value in between

Please help

Tags (1)
3 REPLIES

Re: To Search a String in a different Column String , in which Search String is just part of - SQL Question

HI Swathi,


CREATE VOLATILE TABLE test_string

(

cola CHAR(10)

,colb CHAR(4)

)ON COMMIT PRESERVE ROWS;

INSERT INTO test_string

VALUES( '012abcd4567', 'wxyz');

INSERT INTO test_string

VALUES( '012abcd4567', 'abcd');

The below select retruned me the correct result. 

SEL * FROM test_string

WHERE cola LIKE '%'||TRIM(colb)||'%';

I am not sure, if you are looking for the same.

Thanks,

Narasimha Sarma.

Re: To Search a String in a different Column String , in which Search String is just part of - SQL Question

if its just position, try SEL POSITION(COLB IN COLA)

Re: To Search a String in a different Column String , in which Search String is just part of - SQL Question

Hi Swathi,

As you said these two columns are from different tables and you want to search the matching string in table2.column as per table1.column; I hope the below SQL helps!

create volatile table tab1
(
col_1 char(4)
)on commit preserve rows;

insert into tab1 values('abcd');
insert into tab1 values('efgh');
insert into tab1 values('ijkl');

create volatile table tab2
(
col_x char(20)
)on commit preserve rows;

insert into tab2 values( '1234abcd789ABCD');
insert into tab2 values( '1234efgh789');
insert into tab2 values( '2353098ijkl');

sel t1.col_1, t2.col_x
from tab1 t1, tab2 t2
where t2.col_x like '%'||trim(t1.col_1)||'%';