Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2013
    Posts
    4

    Unanswered: Extract strings from the searchpath column

    Hi, I have a 'searchpath' column in audit table. I need to extract the folders from the column and display it in seperate columns.

    Column searchpath has value like:

    //content/folder[@name='AFR']/folder?[@name='AFRABC']/folder[@name='CDD']/folder[@name='Packages'] /folder[@name='Deployment']/package[@name='XXX spend name']/model

    I need to extract the folder names and display it in seperate columns in a new table this way:
    AFR AFRABC CDD Packages Deployment XXX spend name

    Can you please help in implementing it.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are multiple ways that you can accomplish this. Each has its own costs and benefits from the coding perspective.

    Can your folder names contain an apostrophe? In other words, could a folder be named "This can't exist without a quoting method" exist?

    How will you use the result set produced? Will it be used to join to other tables? Will it have multiple rows of data in a single result set?

    I have far too many questions for me to help much until you can provide some more details. Examples would help a lot, and a use case would help even more.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jul 2013
    Posts
    4
    Thanks for the quick response Pat.
    There are no apostrophes. searchpath column length can vary based on the number of folders.
    I need to join the extracted columns with user login table to find out the distict user names who have accessed these searchpaths.

    I was planning to use a stored procedure for extraction of columns and on top of that a view or materialised view. But need help in writing them.

  4. #4
    Join Date
    Jul 2013
    Posts
    4
    Folder names cannot have apostrophe.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ugh, this really isn't the most efficient method or anything, but it does work
    Code:
    DECLARE @x table (
       val varchar(1000) NOT NULL
    );
    
    INSERT INTO @x (val)
      VALUES ('//content/folder[@name=''AFR'']/folder?[@name=''AFRABC'']/folder[@name=''CDD'']/folder[@name=''Packages'']/folder[@name=''Deployment'']/package[@name=''XXX spend name'']/model')
           , ('//content/folder[@name=''AFR'']/package[@name=''XXX spend name'']/model');
    
    DECLARE @start_pattern  varchar(10) = '[@name='''
          , @ending_pattern varchar(10) = ''']/';
    
    ; WITH x AS (
      SELECT x.val
           , start.number  + Len(@start_pattern) As starting_position
           , ending.number + Len(@ending_pattern) As ending_position
           , Row_Number() OVER (PARTITION BY x.val, start.number ORDER BY ending.number) As row_num
      FROM   @x As x
       INNER
        JOIN dbo.numbers As start
          ON start.number BETWEEN 1 AND Len(x.val)
       INNER
        JOIN dbo.numbers As ending
          ON ending.number BETWEEN 1 AND Len(x.val)
         AND ending.number > start.number
      WHERE  SubString(x.val, start.number , Len(@start_pattern))  = @start_pattern
      AND    SubString(x.val, ending.number, Len(@ending_pattern)) = @ending_pattern
    )
    , y AS (
    SELECT val
         , starting_position
         , ending_position
         , SubString(val, starting_position, ending_position - starting_position - Len(@ending_pattern)) As split_val
         , Row_Number() OVER (PARTITION BY val ORDER BY starting_position) As row_num
    FROM   x
    WHERE  row_num = 1
    )
    SELECT val
         , split_val
         , row_num
    FROM   y
    ORDER
        BY val
         , row_num
    And before you ask, no, I really wouldn't put each value in separate columns. The output from the above is what you want
    George
    Home | Blog

  6. #6
    Join Date
    Jul 2013
    Posts
    4
    Thanks George.

    can you please explain what does start.number, ending.number and dbo.numbers refer to. I do not have any object as dbo.numbers.

    If displaying it into different columns is not a good approach then what should be the ideal approach.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

Posting Permissions

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