Substring

1

I have the sub string below. I want to be able to find out the sub string after counting 10 |
Thanks

SA5|BSI20160524000000000000000000000032|BSI|P50000033|10|1|STX000078|BP|STX000078ITEM01|STX000078ITEM01|Generic Item|1|20160524|20301229|20301229|ea|222|ea|0|0|0|0|0|NOTAX|ZZ||||||Gross|Gross|Gross|Gross|Gross|0|0|0|0|0|Any|||||0|0|0||||BII000110|ZZ|SFBP|||CWAR||||||0||0||||||||||||||2|USA||USA||||||||SA5_END

2

Install a string splitter function if you don't already have it. A good one is the DelimitedSplit8K from here. Once you have it, use it like this:

DECLARE @x VARCHAR(8000) = 'SA5|BSI20160524000000000000000000000032|BSI|P50000033|10|1|STX000078|BP|STX000078ITEM01|STX000078ITEM01|Generic Item|1|20160524|20301229|20301229|ea|222|ea|0|0|0|0|0|NOTAX|ZZ||||||Gross|Gross|Gross|Gross|Gross|0|0|0|0|0|Any|||||0|0|0||||BII000110|ZZ|SFBP|||CWAR||||||0||0||||||||||||||2|USA||USA||||||||SA5_END'

SELECT
	Item
FROM
	dbo.DelimitedSplit8K(@x,'|')
WHERE
	ItemNumber = 10;
3

I believe the ItemNumber should be 11 go get the value after the 10th | :-).

4

Thanks for your response, how can i install the string splitter of DelimitedSplit8K. do you have any example?
thanks

5

Go to this page, scroll down to Figure 21 and copy the code there. In SQL Management Studio, open a query window, change to the database where you want to install the function, paste the code you copied and click the Execute button. This will create the function in your database. You need to do this only once.