Splitting up a string... RegExp??

[JonMusto]

Hi All,

I wonder if someone can help me. I'm a bit of a noob when it comes to SQL server programming.

I'm looking to explode a string by a particular value using an SQL function, e.g.

'aaa/bbb/cccc/ddddd/ee'

Say for example i want to pick out element 4 of this string and return just 'dddd'

It is also possible for these strings to have different amounts of elements and some elements missing. e.g.

'aaa//cccc/ddddd/ee'
'aaa/bbb/cccc'

Is there a way in SQL of exploding these string and producing a variable for each element?


Cheers,
Jonathan.

 

[Geoka]

Declare @Test as Varchar(50)
Declare @Cnt as Int, 
	@Pos as Int,
	@LPos as Int
Set @Cnt=4   -- [COLOR=red]Change this value to get each block [/color]
Set @Cnt = @Cnt - 1
set @Test = 'aaa/bbb/cccc/ddddd/ee'
set @Pos=0
While @Cnt > 0
  begin
    Print @Pos
    select @pos=charindex('/',@Test,@Pos+1)
    Set @Cnt = @Cnt - 1
  End
Select @Lpos = charindex('/',@Test,@Pos+1) 
If @LPos=0
  Set @Lpos = Len(@Test)+1 
Select Substring(@Test,@Pos+1, @LPos-@Pos-1)

 

[JonMusto]

That Works a treat! Thanks very much..