470,591 Members | 1,458 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,591 developers. It's quick & easy.

Syntax issue: URGENT

Hello,

I have a requirement where I need to return all the ids from Table1 if
the column 'name' of Table1 is in (i.e. a substring of) the 'longname'
column of Table2. How do I correct my SQL query as the below one is
showing error:

select Table1.id from Table1, Table2 where (Table2.longname LIKE
'%'||Table1.name||'%')

Thanks in advance,
DW

Aug 1 '06 #1
6 1385
Hello.

select Table1.id
from Table1, Table2
where posstr(Table2.longname, Table1.name)!=0;

Hello,

I have a requirement where I need to return all the ids from Table1 if
the column 'name' of Table1 is in (i.e. a substring of) the 'longname'
column of Table2. How do I correct my SQL query as the below one is
showing error:

select Table1.id from Table1, Table2 where (Table2.longname LIKE
'%'||Table1.name||'%')

Thanks in advance,
DW
Aug 1 '06 #2
de*********@yahoo.com wrote:
Hello,

I have a requirement where I need to return all the ids from Table1 if
the column 'name' of Table1 is in (i.e. a substring of) the 'longname'
column of Table2. How do I correct my SQL query as the below one is
showing error:

select Table1.id from Table1, Table2 where (Table2.longname LIKE
'%'||Table1.name||'%')
There is no pretty way to do this.
The migration toolkit provides a Java function with a generalized LIKE.
You can also get nasty:
CREATE PROCEDURE INSTR_P(IN string VARCHAR(128),
IN substring VARCHAR(128),
OUT found CHAR(1))
DETERMINISTIC CONTAINS SQL NO EXTERNAL ACTION
SET found = CASE WHEN string LIKE '%' || substring || '%'
THEN 1 ELSE 0 END;

--#SET TERMINATOR @
CREATE FUNCTION INSTR(string VARCHAR(128),
substring VARCHAR (128))
RETURNS CHAR(1)
DETERMINISTIC CONTAINS SQL NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE result CHAR(1);
CALL INSTR_P(string, substring, result);
RETURN result;
END
@
--#SET TERMINATOR ;
SELECT
Table1.id from Table1, Table2
where INSTR(Table2.longname, Table1.name) = 1

I didn't test it...

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 1 '06 #3
de*********@yahoo.com wrote:
Hello,

I have a requirement where I need to return all the ids from Table1 if
the column 'name' of Table1 is in (i.e. a substring of) the 'longname'
column of Table2. How do I correct my SQL query as the below one is
showing error:

select Table1.id from Table1, Table2 where (Table2.longname LIKE
'%'||Table1.name||'%')
LIKE requires a constant string (either explicitly specified or originating
from a host variable) as pattern. This is not the case here because your
pattern in dynamic.

You can use the functions POSSTR or LOCATE for the test. Serge offered
another solution. Yet another approach is to use regular expression
matching as is described here:
http://www.ibm.com/developerworks/db...301stolze.html

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Aug 1 '06 #4
select Table1.id
from Table1, Table2
where posstr(Table2.longname, Table1.name)!=0;
I think POSSTR also have similar restriction for search string as LIKE.
So, you should use LOCATE.

select Table1.id
from Table1, Table2
where LOCATE(Table1.name, Table2.longname) 0;

Aug 1 '06 #5
Yes, that's true.
I tested this at my iSeries V5R3.
posstr in iSeries supports string expression (including column names)
in 2-nd argument...

select Table1.id
from Table1, Table2
where posstr(Table2.longname, Table1.name)!=0;
I think POSSTR also have similar restriction for search string as LIKE.
So, you should use LOCATE.

select Table1.id
from Table1, Table2
where LOCATE(Table1.name, Table2.longname) 0;
Aug 2 '06 #6
Dear All:
Thanks for all the responses.
DW

Aug 2 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Amit D.Shinde | last post: by
7 posts views Thread by | last post: by
1 post views Thread by =?Utf-8?B?bGpsZXZlbmQy?= | last post: by
2 posts views Thread by simplebelle | last post: by
13 posts views Thread by SAL | last post: by
3 posts views Thread by =?Utf-8?B?UGF1bCBQcmV3ZXR0?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.