All Forums Database
Swathi Reddy 6 posts Joined 12/13
10 Mar 2016
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:
nanipsns 7 posts Joined 08/13
10 Mar 2016

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.

yuvaevergreen 93 posts Joined 07/09
11 Mar 2016

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

sakthikrr 53 posts Joined 07/12
30 Mar 2016

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)||'%';

Sakthi

You must sign in to leave a comment.