Splitting a Pipe Delimited File into columns

building semantic layer
1

Hello

I have trawled the internet and this site and tried (unsucessfully) to split the pipe delimited field that I have into separate fields.

So far, thanks to some code that I found on google, I have managed to get the last 2 values, and can get the first, It is just all the bits in the middle that I seem to be getting stuck with.

I am using a SQL Server 2008 database and would, ideally, like this to be a Universe solution rather than using substring and Pos in the report itself.

Here is what I have so far:

select 
    RIGHT([DESCRIPTION], Nullif(CHARINDEX('|', REVERSE([DESCRIPTION]))-1, - 1) ) as 'Last',
    RIGHT( 
      LEFT([DESCRIPTION], LEN([DESCRIPTION]) - 
      CHARINDEX('|', REVERSE([DESCRIPTION]))),  
      CHARINDEX('|', REVERSE(LEFT([DESCRIPTION], LEN([DESCRIPTION]) - CHARINDEX('|', REVERSE([DESCRIPTION])))
      )) - 1
    )  as 'Penultimate'
From FINENTRYFACT
   Where FINENTRYFACTID = 1

There are Nine values and, unfortunately, each value is of differing lengths.

What I really need is for someone to show me how to get the 1st, 2nd, 3rd values and then I will be able to see where I am going wrong (I can’t seem to get the Position of the last delimiter in order to start from there for the next column).

I hope that someone can help as this has driven me to distraction this weekend!

Many thanks in advance to anyone that can help.
Jubs

jubline (BOB member since 2010-02-11)

2

I’d suggest that you do it as part of a data load away from the reporting level - you could easily build a stored procedure, function or ssis package to handle this.

3

Hi Mark

I would love to be able to do this, but unfortunately it has to be done in the Universe or (as a last resort) in the Webi Report itself. We go live in 4 weeks with a huge Data Migration project and I am unable to now make any further changes to the underlying DHW or DS code… The only thing left to me is the Universe / Reports. It is a real pain and the time that I have wasted so far is very frustrating.

Thanks for the suggestion though!

Jubs.

jubline (BOB member since 2010-02-11)

4

The first piece of data can be found with (pseudo code shown, may not be 100% complete syntax)

substring (input string, 1, charindex(input string, '|') -1)

What that does is extract from the beginning of the string to the character just before the first | symbol. The rest get tricky, and involve nesting charindex() functions within each other. For example, to find the second | symbol you can do this:

charindex(input string, '|', charindex(input string, '|') +1)

That finds the first | after the first |, or in other words, the second |. That means that the second piece of data can be found with

substring(input string, charindex(input string, '|')+1, charindex(input string, '|', charindex(input string, '|') +1) - charindex(input string, '|'))

Or in other words, take the substring from the character beyond the first | for a length determined by the number of characters from the second | to the first | + 1.

To find the third (and following) | symbols you have to keep chopping off the first parts of the string as you find each | symbol.

Can you create a database function? Even that would be easier that doing all of this work in the universe with the string functions you have available. I found this topic on another board where they discussed splitting a string.

Dave Rathbun :us: (BOB member since 2002-06-06)

5

Many thanks for the help dave, it is so kind of you to provide such a good description of what is needed. I shall have a play with it and will post the final solution - just in case anyone else needs to do a similar thing in the future.

Thanks again!

Jubs

jubline (BOB member since 2010-02-11)

6

Hello again

I had a play with the code, was running out of time so asked a colleague for a suggested solution. Here is the code that he created (which works beautifully) - he is a real saviour!

select 
			[ONE],
			[TWO],
			[THREE],
			[FOUR],
			[FIVE],
			[SIX],
			[SEVEN],
			[EIGHT],
			description as [NINE]
			from 
			(
			
			select 
			[ONE],
			[TWO],
			[THREE],
			[FOUR],
			[FIVE],
			[SIX],
			[SEVEN],
			SUBSTRING(description,0,charindex('|',description,0)) as [EIGHT],			
			SUBSTRING(description,charindex('|',description,0)+1,5000) as description
			from 
			(
			select 
			[ONE],
			[TWO],
			[THREE],
			[FOUR],
			[FIVE],
			[SIX],
			SUBSTRING(description,0,charindex('|',description,0)) as [SEVEN],			
			SUBSTRING(description,charindex('|',description,0)+1,5000) as description
			from 
			(
			select 
			[ONE],
			[TWO],
			[THREE],
			[FOUR],
			[FIVE],
			SUBSTRING(description,0,charindex('|',description,0)) as [SIX],			
			SUBSTRING(description,charindex('|',description,0)+1,5000) as description
			from 
			(
			select 
			[ONE],
			[TWO],
			[THREE],
			[FOUR],
			SUBSTRING(description,0,charindex('|',description,0)) as [FIVE],			
			SUBSTRING(description,charindex('|',description,0)+1,5000) as description
			from 
			(
			select 
			[ONE],
			[TWO],
			[THREE],
			SUBSTRING(description,0,charindex('|',description,0)) as [FOUR],			
			SUBSTRING(description,charindex('|',description,0)+1,5000) as description
			from 
			(

			select 
			[ONE],
			[TWO],
			SUBSTRING(description,0,charindex('|',description,0)) as [THREE],			
			SUBSTRING(description,charindex('|',description,0)+1,5000) as description
			from 
			(

			select 
			[ONE],
			SUBSTRING(description,0,charindex('|',description,0)) as [TWO],			
			SUBSTRING(description,charindex('|',description,0)+1,5000) as description
			from 

			(
			select 
			SUBSTRING(description,0,charindex('|',description,0)) as [ONE],			
			SUBSTRING(description,charindex('|',description,0)+1,5000) as description
			from TABLE
			) two
			) three
			) four
			) five
			) six
			) seven
			) eight
			) nine

I then just import the code as a Derived Table and then use it as I would a normal table in the universe. There is also an ID field that I use as a link, but I removed this from the code above.

Hope that helps someone

Many thanks for all the help!

Jubs

jubline (BOB member since 2010-02-11)

7

Ah, interesting technique. Using nested selects delivers the same results as nested charindex() functions but much easier to read. Nice.

Dave Rathbun :us: (BOB member since 2002-06-06)