locked
Want to parse name field based on a delimeter RRS feed

  • Question

  • I have found threads that explain how to separate names but i cannot make work in this case.  The name field might contain anywhere from only one name with no delimeters to five names with four delimeters.  I want to replace the delimeter with a space and reorder the names.

    Original data format: Name2/Name1/Name3/Name4/Name5.

    Desired data format: Name1 Name2 Name3 Name4 Name5.

    Examples of source data

    1. Company ABC
    2. Doe/John
    3. Smith/Jim/Etal
    4. Jones/Jeff/Jr/& Sally
    5. Bush/Jim/Sr/Etal/Trustee

    thanks in advance.  i thought i posted this elsewhere but cannot find my post.

    GIS Jim


    Jim

    Friday, September 18, 2015 2:29 PM

Answers

  • Patrick, i got error about dbo.splitter.  i will look at more next week.

    Jim

    You need to grab the function from the article I linked to here

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    • Marked as answer by GIS Jim Monday, September 21, 2015 8:50 PM
    Friday, September 18, 2015 10:02 PM
  • In SSMS open your database, then go to Programmability/Functions and check both Scalar-valued functions and Table-valued functions. You may find your functions in one of these 2 categories.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by GIS Jim Monday, September 21, 2015 8:49 PM
    Monday, September 21, 2015 6:35 PM
  • So i understand now the function is in db, but how do i find to delete?  I have created several while doing this testing but do not remember how many or what they are called.

    Jim

    You can check from SSMS for the object with Object Explore Details to find the objects created and you may see what you are creating recently.

    View>>Object Explore Details and click on Stored Procedurs or Function (Table-Valued Functions or Scalar-valued Functions) to check them out.

    • Marked as answer by GIS Jim Monday, September 21, 2015 8:49 PM
    Monday, September 21, 2015 7:07 PM
  • No, but simpler query I meant the one which is just below this your reply, e.g.

    WITH Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n<101),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
    
    
    , mycte as (
    select TXPRCL , TXTNAM, n,  substring(TXTNAM , n, charindex('/', TXTNAM + '/', n) - n)  splitName
    from dbo.PCWEBF21
    cross apply (Select n from Nums ) d(n) 
    Where n <= len(TXTNAM ) AND substring('/' + TXTNAM , n, 1) = '/' ),
     
    
     cte as (select *, row_Number() over (partition by TXPRCL order by N) as Rn from mycte)
    
    select TXPRCL, TXTNAM as OriginalName,
    RTRIM(ISNULL(min(case when Rn=2 then SplitName end) + ' ','') + 
    ISNULL(min(case when Rn=1 then SplitName end) + ' ','') + 
    ISNULL(min(case when Rn=3 then SplitName end) + ' ','') + 
    ISNULL(min(case when Rn=4 then SplitName end) + ' ','') + 
    ISNULL(min(case when Rn=5 then SplitName end),'')) as NewName
    from cte
    GROUP BY TxPrcl, TxtNam
    ORDER BY TxPrcl, OriginalName


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles



    • Marked as answer by GIS Jim Monday, September 21, 2015 8:49 PM
    • Edited by Naomi N Monday, September 21, 2015 9:26 PM
    Monday, September 21, 2015 7:34 PM
  • Sorry, I fixed typos (in both messages as I just copied it the second time). Since I didn't test last solution but rather typed from the top of my head, I put ) in the wrong place originally.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Monday, September 21, 2015 8:59 PM
    • Marked as answer by GIS Jim Monday, September 21, 2015 9:23 PM
    Monday, September 21, 2015 8:59 PM

All replies

  • create table test (ID int, Name varchar(100))
    Insert test values(1,'Company ABC'),(2,'Doe/John'),(3,'Smith/Jim/Etal'),(4,'Jones/Jeff/Jr/& Sally'),(5,'Bush/Jim/Sr/Etal/Trustee')
     
    ---===== Create number table on-the-fly
    ;WITH Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n<101),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
    
    
    , mycte as (
    select ID,    substring(Name, n, charindex('/', Name + '/', n) - n)  splitName
    from test
    cross apply (Select n from Nums ) d(n) 
    Where n <= len(Name) AND substring('/' + Name, n, 1) = '/' )
     
    
    SELECT t1.ID, 
           Stuff(( SELECT ' ' + Cast(t2.splitName as varchar(5))
               FROM mycte t2
              WHERE t2.ID = t1.ID  
              ORDER BY splitName
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS Names
      FROM mycte t1
     GROUP BY t1.ID 
     
     
      
     
    drop table test

    • Proposed as answer by Naomi N Friday, September 18, 2015 3:28 PM
    • Unproposed as answer by Naomi N Friday, September 18, 2015 3:40 PM
    Friday, September 18, 2015 2:39 PM
  • REPLACE(YourColumn, '/', SPACE(0))

    A Fan of SSIS, SSRS and SSAS

    Friday, September 18, 2015 3:21 PM
  • Guoxiong, along with the space, i need to reorder the names and switch one and two around only if there is >= one delimeter.

    Jim

    Friday, September 18, 2015 3:27 PM
  • Jingyang Li, i tried the code and came up with results that don't look right.

    1. Compa
    2. Doe John
    3. Etal Jim Smith
    4. & Sal Jeff Jones Jr
    5. Bush Etal Jim Sr Trust

    Should be

    1. Company ABC
    2. John Doe
    3. Jim Smith Etal
    4. Jeff Jones Jr & Sally
    5. Jim Bush Sr Etal Trustee

    Jim

    Friday, September 18, 2015 3:34 PM
  • What are the outputs of your examples of source data?

    A Fan of SSIS, SSRS and SSAS

    Friday, September 18, 2015 3:34 PM
  • Try this modification:

    create table test (ID int, Name varchar(100))
    Insert test values(1,'Company ABC'),(2,'Doe/John'),(3,'Smith/Jim/Etal'),(4,'Jones/Jeff/Jr/& Sally'),(5,'Bush/Jim/Sr/Etal/Trustee')
     
    ---===== Create number table on-the-fly
    ;WITH Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n<101),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
    
    
    , mycte as (
    select ID,  n,  substring(Name, n, charindex('/', Name + '/', n) - n)  splitName
    from test
    cross apply (Select n from Nums ) d(n) 
    Where n <= len(Name) AND substring('/' + Name, n, 1) = '/' ),
     
    
     cte as (select *, row_Number() over (partition by Id order by N) as Rn from mycte)
    
    SELECT t1.ID, ISNULL(min(case when t1.Rn = 2 then t1.splitName end) + ' ','') +  
           Stuff(( SELECT ' ' + t2.splitName
               FROM cte t2
              WHERE t2.ID = t1.ID  and t2.Rn <> 2
              ORDER BY Rn
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS Name
      FROM cte t1
     GROUP BY t1.ID 
     
     
      
     
    drop table test


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles



    • Edited by Naomi N Friday, September 18, 2015 4:01 PM
    Friday, September 18, 2015 3:44 PM
  • I have increase the size inside the code but you need to check your sample data as well.

     
     create table test (ID int, Name varchar(100))
    Insert test values(1,'Company ABC'),(2,'Doe/John'),(3,'Smith/Jim/Etal'),(4,'Jones/Jeff/Jr/& Sally'),(5,'Bush/Jim/Sr/Etal/Trustee')
     
    ---===== Create number table on-the-fly
    ;WITH Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n<101),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
    
    
    , mycte as (
    select ID,    substring(Name, n, charindex('/', Name + '/', n) - n)  splitName
    from test
    cross apply (Select n from Nums ) d(n) 
    Where n <= len(Name) AND substring('/' + Name, n, 1) = '/' )
     
    
    SELECT t1.ID, 
           Stuff(( SELECT ' ' + Cast(t2.splitName as varchar(100))
               FROM mycte t2
              WHERE t2.ID = t1.ID  
              ORDER BY splitName
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS Names
      FROM mycte t1
     GROUP BY t1.ID 
     
     
      
     
    drop table test
    
    /*
    ID	Names
    1	Company ABC
    2	Doe John
    3	Etal Jim Smith
    4	& Sally Jeff Jones Jr
    5	Bush Etal Jim Sr Trustee
    */

    Friday, September 18, 2015 3:51 PM
  • Thanks, this appears to work with the sample data.  Could you guide me to substitute the source table?

    Jim

    Friday, September 18, 2015 4:22 PM
  • All you need to change in that script is this line

    from test

    change to your table name. And, of course, don't create/delete test table.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, September 18, 2015 4:26 PM
  • Is it possible to create a View of the original table instead of the new table?  I was hoping to create a view that incorporates the code i have already written with the name parsing.  Where the two source fields TXTNAM & TXANAM are combined into a new field TXTNAM_TXANAM.  If that is not possible, then I would need to create the Name field in a separate table and join to the rest of the fields shown here.
    SELECT
      CAST(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TXPRCL, '  ', ' '), '  ', ' '), '  ', ' '), '  ', ' '), '  ', ' '), '  ', ' '))) AS VARCHAR(11))AS TXPRCL,
    
      CASE
        WHEN TXALTR = 0
          then CAST(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TXTAXP,'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' ')))AS INT)
        ELSE  CAST(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TXALTR,'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' ')))AS INT)
      END
        As TXTAXP_TXALTR,
    
      CASE
        WHEN TXALTR = 0
          then CAST(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TXTNAM,'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' ')))AS VARCHAR(30))
        ELSE CAST(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TXANAM,'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' ')))AS VARCHAR (30))
      END
        As TXTNAM_TXANAM,
    
      CASE
        WHEN TXALTR = 0
          then  CAST(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TXTAD1,'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' ')))AS VARCHAR (39))
        ELSE CAST(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TXAAD1,'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' ')))AS VARCHAR (39))
      END
        As TXTAD1_TXAAD1,
    
      CASE
        WHEN TXALTR = 0
          then CAST(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TXTAD2,'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' ')))AS VARCHAR (39))
        ELSE CAST(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TXAAD2,'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' ')))AS VARCHAR (39))
      END
        As TXTAD2_TXAAD2,
    
      CASE
        WHEN TXALTR = 0
          then CAST(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TXTAD3,'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' ')))AS VARCHAR (39))
        ELSE CAST(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TXAAD3,'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' ')))AS VARCHAR (39))
      END
        As TXTAD3_TXAAD3,
    
      CASE
        WHEN TXALTR = 0
          then CAST(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TXTAD4,'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' ')))AS VARCHAR (39))
        ELSE CAST(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TXAAD4,'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' ')))AS VARCHAR (39))
      END
        As TXTAD4_TXAAD4
    
    FROM dbo.PCWEBF21
    



    Jim

    Friday, September 18, 2015 4:36 PM
  • I don't understand your new question, but you should be able to incorporate the code above to create your view. You don't need to create a new table.

    Also, if you have a permanent Numbers table in your database, then use it instead of the CTE.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, September 18, 2015 4:45 PM
  • I think I might have to give up on this one unless there is something simply wrong with the code.  I have not learned this yet.

    ---===== Create number table on-the-fly
    ;WITH Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n<101),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
    
    
    , mycte as (
    select TXPRCL ,  n,  substring(TXTNAM, n, charindex('/', TXTNAM+ '/', n) - n)  TXTNAM
    from dbo.PCWEBF21
    cross apply (Select n from Nums ) d(n) 
    Where n <= len(TXTNAM) AND substring('/' + TXTNAM, n, 1) = '/' ),
     
    
     dbo.PCWEBF21 as (select *, row_Number() over (partition by TXPRCL order by N) as TXTNAM from mycte)
    
    SELECT t1.TXPRCL , ISNULL(min(case when t1.TXTNAM = 2 then t1.TXTNAM end) + ' ','') +  
           Stuff(( SELECT ' ' + t2.TXTNAM
               FROM dbo.PCWEBF21 t2
              WHERE t2.TXPRCL = t1.TXPRCL and t2.TXPRCL <> 2
              ORDER BY TXPRCL 
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS TXTNAM
      FROM dbo.PCWEBF21 t1
     GROUP BY t1.TXPRCL 


    Jim

    Friday, September 18, 2015 5:40 PM
  • Why did you name your second CTE as dbo.PCWebF21 and why did you name Row_Number() function result the same as your name column?

    Look one more time to the solution posted and try to understand it. In order to understand, after the second cte (myCte), just use

    select * from myCte to understand its result.

    I don't know why such simple code should be mangled completely in your implementation. All you need to do is to have some primary key column (ID) in your table that uniquely identifies the rows and some name column.

    So, if you're unable to adapt such simple code as I shown to you to your table, post your table structure and name and some input, I'll show what do you need to use in the code.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Friday, September 18, 2015 5:57 PM
    Friday, September 18, 2015 5:57 PM
  • Oops, i should have Googled what CTE & RN meant.  So i will substitute TXPRCL which is text for the ID field and substitute TXTNAM for the Name Field and substitute dbo.PCWEBF21 for the table test.  What is the function of splitName and t1 and t2?

    Jim

    forgot table info


    TXPRCL C 12 TXTNAM C 30 delimeters for TXTNAM (name) WEST/DAVID L/SR/ETAL/TRSTEES BURKE/STEVEN A/ETAL/TRUSTEES ZUNKER/MICHAEL L/ETAL ZWIEG/ROGER ZURBAY FAMILY LLC

    TXPRCL (TaxID)

    03-0001-000


    • Edited by GIS Jim Friday, September 18, 2015 6:42 PM add info
    Friday, September 18, 2015 6:34 PM
  • t1 and t2 are the aliases for the same CTE that is used to concatenate names back together.

    The first part of the query simply splits the name into a table where each part of the name between / becomes a new row. You don't have to use this particular technique, you can use any of the techniques to split a name using some delimiter. In other words, if you already have fnSplit function in your database, just use that function like so

    select T., F.*

    from myTable T

    CROSS APPLY dbo.fnSplit(T.NameColumn, '/') as F

    ----------------------------------------------------------

    For fnSplit function you can just search this forum, there were numerous samples already.

    So, this is the first step - to split the name into individual portions, each of these is called splitName in our sample code.

    The second step is to concatenate these names back together using another very common technique of XML PATH('') to concatenate the names. Since we want to put the second name to be the first and then the rest, I removed it in the second part of the query (where Rn <> 2) and used it in the MIN(case when Rn=2 then name end) to add in front of the other names.

    So, is it clear now what the query is doing and how you should adapt it for your table name?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, September 18, 2015 6:45 PM
  • How does this look?  It times out with this code.SQL Error

    ---===== Create number table on-the-fly
    ;WITH Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n<101),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
    
    
    , mycte as (
    select TXPRCL ,  n,  substring(TXTNAM , n, charindex('/', TXTNAM + '/', n) - n)  splitName
    from dbo.PCWEBF21
    cross apply (Select n from Nums ) d(n) 
    Where n <= len(TXTNAM ) AND substring('/' + TXTNAM , n, 1) = '/' ),
     
    
     cte as (select *, row_Number() over (partition by TXPRCL order by N) as Rn from mycte)
    
    SELECT t1.TXPRCL , ISNULL(min(case when t1.Rn = 2 then t1.splitName end) + ' ','') +  
           Stuff(( SELECT ' ' + t2.splitName
               FROM cte t2
              WHERE t2.TXPRCL = t1.TXPRCL and t2.Rn <> 2
              ORDER BY Rn
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS TXTNAM 
      FROM cte t1
     GROUP BY t1.TXPRCL 

    i also got error that splitName is not in the table when selecting * from the table.


    Jim

    Friday, September 18, 2015 7:07 PM
  • Give this a go:

    DECLARE @names TABLE (nameString NVARCHAR(100))
    INSERT INTO @names (nameString) VALUES
    ('Company ABC			  '),
    ('Doe/John				  '),
    ('Smith/Jim/Etal		  '),
    ('Jones/Jeff/Jr/& Sally	  '),
    ('Bush/Jim/Sr/Etal/Trustee')
    
    SELECT nameString, MAX(name1) AS name1, MAX(name2) AS name2, MAX(name3) AS name3, MAX(name4) AS name4, MAX(name5) AS name5
      FROM (
    SELECT nameString, id, 'name'+CAST(id AS CHAR(1)) AS colName, value
      FROM @names
        CROSS APPLY dbo.splitter(nameString,'/')
           ) s
        PIVOT (
    	       MAX(value) FOR colName IN (name1, name2, name3, name4, name5)
    			) p
     GROUP BY nameString
    It uses my splitter function, from this article


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Friday, September 18, 2015 7:14 PM
  • How big is your table (how many rows)?

    In our case we're re-using the same CTE twice and it may be a serious performance hit. It may be a good idea to use a temporary table before implementing the second step, e.g.

    IF OBJECT_ID('TempDb.#FirstResult') IS NOT NULL DROP TABLE #FirstResult;
    
    ;WITH Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n<101),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
    
    
    , mycte as (
    select TXPRCL ,  n,  substring(TXTNAM , n, charindex('/', TXTNAM + '/', n) - n)  splitName
    from dbo.PCWEBF21
    cross apply (Select n from Nums ) d(n) 
    Where n <= len(TXTNAM ) AND substring('/' + TXTNAM , n, 1) = '/' )
     
    
    select *, row_Number() over (partition by TXPRCL order by N) as Rn into #FirstResult from mycte

    and then substitute cte with #FirstResult in the second part of the query.

    Also, is  TXPRCL unique in the table?


     

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles



    • Edited by Naomi N Friday, September 18, 2015 7:56 PM
    Friday, September 18, 2015 7:17 PM
  • If you're having issues subbing in your own objects, try:

    SELECT TXTNAM, MAX(name1) AS name1, MAX(name2) AS name2, MAX(name3) AS name3, MAX(name4) AS name4, MAX(name5) AS name5
      FROM (
    SELECT TXTNAM, id, 'name'+CAST(id AS CHAR(1)) AS colName, value
      FROM dbo.PCWEBF21
        CROSS APPLY dbo.splitter(TXTNAM,'/')
           ) s
        PIVOT (
    	       MAX(value) FOR colName IN (name1, name2, name3, name4, name5)
    			) p
     GROUP BY TXTNAM


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Friday, September 18, 2015 7:22 PM
  • If the maximum number of name parts is 5, then use simpler query:

    ;WITH Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n<101),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
    
    
    , mycte as (
    select TXPRCL , TXTNAM, n,  substring(TXTNAM , n, charindex('/', TXTNAM + '/', n) - n)  splitName
    from dbo.PCWEBF21
    cross apply (Select n from Nums ) d(n) 
    Where n <= len(TXTNAM ) AND substring('/' + TXTNAM , n, 1) = '/' ),
     
    
     cte as (select *, row_Number() over (partition by TXPRCL order by N) as Rn from mycte)
    
    select TXPRCL, TXTNAM as OriginalName,
    RTRIM(ISNULL(min(case when Rn=2 then SplitName end) + ' ','') + 
    ISNULL(min(case when Rn=1 then SplitName end) + ' ','') + 
    ISNULL(min(case when Rn=3 then SplitName end) + ' ','') + 
    ISNULL(min(case when Rn=4 then SplitName end) + ' ','') + 
    ISNULL(min(case when Rn=5 then SplitName end),'')) as NewName
    from cte
    GROUP BY TxPrcl, TxtNam
    ORDER BY TxPrcl, OriginalName

    number of name parts is 5 in each name, try

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles



    • Edited by Naomi N Monday, September 21, 2015 9:26 PM
    Friday, September 18, 2015 7:33 PM
  • Naomi, End of work week is here, but i tried and got incorrect syntax near SELECT.  TXPRCL is one of two fields that make a unique combo.  I could query off a view where the TXPRCL is unique. thanks,

    jim


    Jim

    Friday, September 18, 2015 7:54 PM
  • Patrick, i got error about dbo.splitter.  i will look at more next week.

    Jim

    Friday, September 18, 2015 7:56 PM
  • I fixed the typo in the above code, but if you have only 5 names at most, I suggest to use another version of the query I posted which should execute much quicker.

    Also, if your TxPrcl column is not unique, you can not use this code as you need a unique column(s). But I don't recommend to use a view as a query which is based on a view may perform worse than a query based on a regular table.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, September 18, 2015 7:58 PM
  • Patrick, i got error about dbo.splitter.  i will look at more next week.

    Jim

    You need to grab the function from the article I linked to here

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    • Marked as answer by GIS Jim Monday, September 21, 2015 8:50 PM
    Friday, September 18, 2015 10:02 PM
  • Patrick, I do need to learn more about calling functions but maybe you could help me understand.  Where does the function reside and how long does it remain there?  I am going to have to delete and recreate because i need more fields in the view.

    The situation, there is a dbo table that has more than one primary key field so to get a unique field i have created a view of the table where i queried out the fields i need and it only has one unique field instead of two.  Since this method eliminates duplicates, it is apparently using the field TXTNAM as the unique field.

    Also I do not know why it is cutting off left characters in the second and third name fields.

    ABEL/STEVEN G & GLENDA G     looks like -- ABEL G & GLENDA G     

    ABBOTT/ROBERT K/JR & SUSAN J  -- looks like-- ABBOTT BERT K

    thanks for your suggestions.  i will look at the others.


    Jim

    Monday, September 21, 2015 6:10 PM
  • I didn't look at that function, but I suspect it's using recursive approach. In this case there is a possibility of incorrect result returned unless the value is explicitly converted to a particular varchar(n) type where n is a maximum possible length.

    Once you created a function in a database, it stays there until you explicitly delete it (ever in the interface or using DROP FUNCTION command).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, September 21, 2015 6:14 PM
  • So i understand now the function is in db, but how do i find to delete?  I have created several while doing this testing but do not remember how many or what they are called.

    Jim

    Monday, September 21, 2015 6:29 PM
  • In SSMS open your database, then go to Programmability/Functions and check both Scalar-valued functions and Table-valued functions. You may find your functions in one of these 2 categories.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by GIS Jim Monday, September 21, 2015 8:49 PM
    Monday, September 21, 2015 6:35 PM
  • A function "resides" in the database and schema you create it in.

    USE database1
    
    CREATE FUNCTION schema2.returnOne()
    RETURNS INT
    BEGIN
    RETURN 1
    END
    

    Creates the function returnOne on the schema schema2 in database1, for example.

    It will remain there until dropped.

    I'm not sure what the second part of your question is referring to. Could you clarify?


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Monday, September 21, 2015 6:54 PM
  • I didn't look at that function, but I suspect it's using recursive approach. In this case there is a possibility of incorrect result returned unless the value is explicitly converted to a particular varchar(n) type where n is a maximum possible length.

    splitter() and splitterMkII both use VARCHAR(MAX) data types. Unless the string exceeds 2gigs, it won't be truncated.

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Monday, September 21, 2015 6:56 PM
  • So i understand now the function is in db, but how do i find to delete?  I have created several while doing this testing but do not remember how many or what they are called.

    Jim

    You can check from SSMS for the object with Object Explore Details to find the objects created and you may see what you are creating recently.

    View>>Object Explore Details and click on Stored Procedurs or Function (Table-Valued Functions or Scalar-valued Functions) to check them out.

    • Marked as answer by GIS Jim Monday, September 21, 2015 8:49 PM
    Monday, September 21, 2015 7:07 PM
  • Naomi, i believe this is the simpler query you were referring to.  I am calling the data from a simple table with unique numbers in TxPrcl, with 15 records.  I get a message that 30 rows affected by last query, but do not see any records.

    Then when i try create new view in Arc Catalog, it errors saying 'Incorrect syntax near keyword IF'.

    IF OBJECT_ID('TempDb.#FirstResult') IS NOT NULL DROP TABLE #FirstResult;
    
    ;WITH Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n<101),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
    
    , mycte as (
    select TXPRCL ,  n,  substring(TXTNAM , n, charindex('/', TXTNAM + '/', n) - n)  splitName
    from GISADMIN.Test_15_Records_PCWEBF21
    cross apply (Select n from Nums ) d(n) 
    Where n <= len(TXTNAM ) AND substring('/' + TXTNAM , n, 1) = '/' )
     
    select *, row_Number() over (partition by TXPRCL order by N) as Rn into #FirstResult from mycte

    If this gets too involved, i will just use the Names as is with the delimeters in and tell users they have to deal with it.  Sorry for all the questions on this one issue.  It appears to be my last one to clean up the table appearance.  Of course, the original table gets dumped on a weekly basis so i will need to acces all the data through the Views i am creating.

    Jim


    Jim

    Monday, September 21, 2015 7:26 PM
  • No, but simpler query I meant the one which is just below this your reply, e.g.

    WITH Num1 (n) AS (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n<101),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
    
    
    , mycte as (
    select TXPRCL , TXTNAM, n,  substring(TXTNAM , n, charindex('/', TXTNAM + '/', n) - n)  splitName
    from dbo.PCWEBF21
    cross apply (Select n from Nums ) d(n) 
    Where n <= len(TXTNAM ) AND substring('/' + TXTNAM , n, 1) = '/' ),
     
    
     cte as (select *, row_Number() over (partition by TXPRCL order by N) as Rn from mycte)
    
    select TXPRCL, TXTNAM as OriginalName,
    RTRIM(ISNULL(min(case when Rn=2 then SplitName end) + ' ','') + 
    ISNULL(min(case when Rn=1 then SplitName end) + ' ','') + 
    ISNULL(min(case when Rn=3 then SplitName end) + ' ','') + 
    ISNULL(min(case when Rn=4 then SplitName end) + ' ','') + 
    ISNULL(min(case when Rn=5 then SplitName end),'')) as NewName
    from cte
    GROUP BY TxPrcl, TxtNam
    ORDER BY TxPrcl, OriginalName


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles



    • Marked as answer by GIS Jim Monday, September 21, 2015 8:49 PM
    • Edited by Naomi N Monday, September 21, 2015 9:26 PM
    Monday, September 21, 2015 7:34 PM
  • I do not know why the error says MIN requires one argument.  Isn't the(case...) an argument.

    Jim

    Monday, September 21, 2015 8:04 PM
  • The thread is getting too long and also due to the forum's bug I can not see your last reply. I suggest to mark the answers in this thread and start a new one fresh with what is your current problem.

    Basically, your problem is very simple - you first need to split the name based on the '/' using any of the available splitter function and then assuming that max number of names is 5 you use the idea from my last reply of summing MAX (case ...) 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by GIS Jim Monday, September 21, 2015 8:48 PM
    • Unmarked as answer by GIS Jim Monday, September 21, 2015 9:02 PM
    Monday, September 21, 2015 8:25 PM
  • Sorry, I fixed typos (in both messages as I just copied it the second time). Since I didn't test last solution but rather typed from the top of my head, I put ) in the wrong place originally.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Monday, September 21, 2015 8:59 PM
    • Marked as answer by GIS Jim Monday, September 21, 2015 9:23 PM
    Monday, September 21, 2015 8:59 PM
  • Thanks Naomi.  I see the typo with the MIN and I also found the typo with the Last Field TxtPrcl should be TxPrcl.

    this definitely works!  Now i just need to add the other fields to this and i am set.


    Jim

    Monday, September 21, 2015 9:23 PM
  • It only took us about 40+ messages to get the job done :)

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, September 21, 2015 9:27 PM
  • Actually you just need to take care of the first two parts which need to be switched. Here is the function to replace your string to your target output for any number of delimiter:

    CREATE FUNCTION dbo.fn_ReplaceString (@InputString varchar(1000), @Delimiter char(1), @ReplacedDelimiter char(1))
    RETURNS VARCHAR
    WITH EXECUTE AS CALLER
    AS
    BEGIN
    
    	DECLARE @ReturnString varchar(1000)
    	DECLARE @CurrentString varchar(1000)
    	DECLARE @Item1 varchar(100)
    	DECLARE @Item2 varchar(100)
    	
    	SET @ReturnString = ''
    
    	IF PATINDEX('%' + @Delimiter + '%', @InputString) = 0
    	BEGIN
    		SET @ReturnString = @InputString
    	END
    	ELSE BEGIN
    		SET @Item1 = SUBSTRING(@InputString, 0, PATINDEX('%' + @Delimiter + '%', @InputString))
    		SET @CurrentString = SUBSTRING(@InputString, LEN(@Item1 + @Delimiter) + 1, LEN(@InputString))
    		
    		IF PATINDEX('%' + @Delimiter + '%', @CurrentString) = 0
    		BEGIN
    			SET @ReturnString = @CurrentString + @ReplacedDelimiter + @Item1
    		END
    		ELSE BEGIN
    	     	SET @Item2 = SUBSTRING(@CurrentString, 0, PATINDEX('%' + @Delimiter + '%', @CurrentString))
    	     	SET @ReturnString = @Item2 + @ReplacedDelimiter + @Item1 + @ReplacedDelimiter + REPLACE(@CurrentString, @Delimiter, @ReplacedDelimiter)
    		END
    
    	END
    
    	RETURN @ReturnString
    END


    A Fan of SSIS, SSRS and SSAS

    Monday, September 21, 2015 9:41 PM