Break string by delimiter to make dimension hierarchy

[citic]

I want to break a string in one column in a Table of SQL Server 2000 and make it as indivual columns in the table. Then I can use new created columns to make a dimension table with hierarchy in Analysis Service. The string is delimited by "/". The length and begining letter of the string are different.How can I break the string as indivual elements. Can I use "tokenize function" or "parse string tokens"? Please help. I list some of the string in one column as follows

/efacts/images/map/reg4.jpg
/_vi_bin/..%5C../system/cmd.exe
/scripts/..%5C../winnt/system/cmd.exe

It should looks as four columns:

All Levels
/efacts
/images
/map
/reg4.jpg

Thanks in advance.

 

[tlbroadbent]

Check out the SQL 2000 parse function at Planet Source Code. It may be what you need or at least provide a starting point.

http://www.planet-source-code.com/xq/ASP/txtCodeId.270/lngWid.5/qx/vb/scripts/ShowCode.htm

Other parse scripts are available at SWYNK.com.

http://www.swynk.com/sqlscripts/misc7.asp

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.