Pipe answer in 1 column update

1

Pipe answer are in 1 Column

I want to update another column with the information what is
the 6th pipe in the column (See below) so I want to return the value 15 in Q1 Column

e.g

EXTRA TABLET |24 Month Contract|0|0|10GB - DOUBLE SPEED|15|TT15TB2420||

2

use a splitter:

Splitter

3

You can use XML to do that:
e.g.

DECLARE @A NVARCHAR(500) = 'EXTRA TABLET |24 Month Contract|0|0|10GB - DOUBLE SPEED|15|TT15TB2420|'

    ;WITH CTE AS
    (
    SELECT @A AS A
    ,CAST('<M>' + REPLACE( @A,  '|' , '</M><M>') + '</M>' AS XML) AS X
    )
    
    SELECT
       
        Split.a.value('.', 'VARCHAR(100)') AS Names
    FROM Cte
    CROSS APPLY X.nodes('/M') Split(a)

How To Split A Comma Delimited String

4

@Mangal

That's cool! Have you compared the performance of this method with the Tally Oh splitter? Also, I think you need to add a ROW_NUMBER function call to the final select to be able to get the value in the desired ordinal position.

I guess you can't use this approach if the original text contains less than signs, though