0

So I am trying to split some data into different columns from a pipe delimited row, this is the row: Row

I have a function that works for it which is the following: 

CREATE FUNCTION dbo.TestPipeSplit
(
  @multiwordstring VARCHAR(255),
  @wordnumber      NUMERIC
)
returns VARCHAR(255)
AS

  BEGIN
  DECLARE @remainingstring VARCHAR(255)
  SET @remainingstring=@multiwordstring

  DECLARE @numberofwords NUMERIC
  SET @numberofwords=(LEN(@remainingstring) - LEN(REPLACE(@remainingstring, '|', '')) + 1)

  DECLARE @word VARCHAR(50)
  DECLARE @parsedwords TABLE
  (
     line NUMERIC IDENTITY(1, 1),
     word VARCHAR(255)
  )

  WHILE @numberofwords > 1
    BEGIN
        SET @word=LEFT(@remainingstring, CHARINDEX('|', @remainingstring) - 1)

        INSERT INTO @parsedwords(word)
        SELECT @word

        SET @remainingstring= REPLACE(@remainingstring, Concat(@word, '|'), '')
        SET @numberofwords=(LEN(@remainingstring) - LEN(REPLACE(@remainingstring, '|', '')) + 1)

        IF @numberofwords = 1
          BREAK

        ELSE
          CONTINUE
    END

As you can see on the screenshot, there is a yellow highlight, I need to be able to also separate these rows by dash. So the tool I'm using imports data into SQL, every dash separated item is a piece of user info, I need to be able to separate every user (dash) and then separate the pipe delimited pieces of info into different columns. I was able to do the second part but I'm stuck with the dashes. I tried to apply the same function for the pipes to the dashes but I've left the script running for more than an hour and nothing comes up.

Thank you !!!

Ryan Wilson
  • 10,223
  • 2
  • 21
  • 40
Cesar
  • 1
  • Search for an Inline Table Valued Function for splitting string lists. There are hundreds on line. – MatBailie Sep 12 '18 at 17:22
  • You should really parse this in an application layer of some sort with a quick script, turn out something clean, even CSV, that's suitable for import. – tadman Sep 12 '18 at 17:52

1 Answers1

1

Here is one method using a little XML

This also assume you have known or max number of positions. Otherwise you would need to DYNAMIC

Furthermore, posting an image is not helpful.

Example

Declare @YourTable table (ID int,SomeCol varchar(max))
Insert Into @YourTable values
 (1,'123|abc|456')
,(2,'789|def|012')

Select A.ID
     ,B.*
 From @YourTable A
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                      ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
                      ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
                      ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
                      ,Pos8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
                      ,Pos9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
                From  (Select Cast('<x>' + replace((Select replace(SomeCol,'|','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

Returns

ID  Pos1    Pos2    Pos3    Pos4    Pos5    Pos6    Pos7    Pos8    Pos9
1   123     abc     456     NULL    NULL    NULL    NULL    NULL    NULL
2   789     def     012     NULL    NULL    NULL    NULL    NULL    NULL

If you rather have it as a TVF take a peek at How to extract values from column and update result in another column

John Cappelletti
  • 79,615
  • 7
  • 44
  • 66