SQL to get substrings from within a table

rocker86 · #1 (**permalink**) 02-26-11, 01:27

Hi,
Here's what my table looks like:

Table:

Id-------num---------Path
100------1------------/1/
101------2------------/1/2/
102------2------------/2/
103------3------------/1/2/3/
104------4------------/1/2/3/4/
105------4------------/3/4/
106------4------------/1/4/

now I want a result in which the path is the ending subpath of an existing path. My resultset is something like this:

Id-------num----------Path
102------2------------/2/
105------4------------/3/4/

So the path /2/ was returned because it was the ending path of Id 101
And path /3/4/ was returned for the same reason, similar ending path for Id 104.

I tried the following query,

select A.* from table A where A.Id in(
select B.Id from table B
where
A.PATH like concat('%',B.PATH))

But it throws an SQLSTATE 56098 exception.
could any one please suggest an alternate query to do this?
Would really appreciate some help.
Thanks

tonkuma · #2 (**permalink**) 02-26-11, 03:53

Quote:

But it throws an SQLSTATE 56098 exception.

I don't know the reason of SQLSTATE 56098.

But, you can't specify a column name(or concat with a column name) to the right of a LIKE predicate.
If specified, you would get SQL0132N.

Quote:

SQL0132N A LIKE predicate or POSSTR scalar function is not valid because the
first operand is not a string expression or the second operand is not a
string.
.....

Here is an alternative:

Code:

------------------------------ Commands Entered ------------------------------
WITH
/**************************************************
**********    Start of sample data.      **********
**************************************************/
table(Id , num , Path) AS (
VALUES
  (100 , 1 , '/1/')
, (101 , 2 , '/1/2/')
, (102 , 2 , '/2/')
, (103 , 3 , '/1/2/3/')
, (104 , 4 , '/1/2/3/4/')
, (105 , 4 , '/3/4/')
, (106 , 4 , '/1/4/')
)
/**************************************************
**********      End of sample data.      **********
**************************************************/

SELECT a.*
 FROM  table a
 WHERE EXISTS
       (SELECT 0
         FROM  table b
         WHERE b.num = a.num
           AND LOCATE(a.path , b.path , 2)
               = LENGTH(b.path) - LENGTH(a.path) + 1
       )
;
------------------------------------------------------------------------------

ID          NUM         PATH     
----------- ----------- ---------
        102           2 /2/      
        105           4 /3/4/    

  2 record(s) selected.

rocker86 · #3 (**permalink**) 02-28-11, 04:13

Thanks a ton tonkuma! That worked like a charm

Internet Services

Web Development

Digital Marketing

Consumer Tech