Problem getting some numbers/strings

Hello

i got some strings like this

12|56112 Lahnstein-Oberlahnstein|Nordallee 3|rz-Kasten
6|57612 ?lsen|Dorfstrasse 2|rz-Kasten

i want to get all between this stripes "|".
At the End it should look like this

strRoute strPLZ strOrt strStrasse strInfo

12 57612 ?lsen Dorfstrasse 2 rz-Kasten

with this Query i get the first 1-2 Numbers how can i get the other stuff like PLZ Ort....

select strBeschreibung,

CASE when Patindex ('%|%',strBeschreibung)=0 THEN strBeschreibung
Else Rtrim (Substring(strBeschreibung,1,Patindex ('%|%', strBeschreibung)-1)) END AS strRoute

from depot

Thx Gubi

ps:sorry for my bad english

I would suggest using Jens Suessmeyer's SPLIT function. Use your '|' character as the delimiter. A couple examples are:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1

|||

You are attempting to use a string as a simulated array. Since SQL Server does not have an array datatype, and it also does not have built in functionality to work with arrays, several folks have developed 'work-arounds'. Here are a few that could help:

Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
http://www.realsqlguy.com/?p=9
http://www.aspfaq.com/2248
http://www.projectdmx.com/tsql/sqlarrays.aspx
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/ParseDelimitedStringToTable
2005- http://omnibuzz-sql.blogspot.com/2006/06/interesting-queries-using-recursive.html
http://solidqualitylearning.com/Blogs/dejan/archive/2004/10/22/200.aspx

sql