Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2009
    Posts
    44

    Unanswered: SQL to get substrings from within a table

    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

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    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.
    Last edited by tonkuma; 02-26-11 at 05:00. Reason: Change second predicate in WHERE clause of EXISTS subquery.

  3. #3
    Join Date
    Jul 2009
    Posts
    44
    Thanks a ton tonkuma! That worked like a charm

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •