Finding Specific Strings in Another Strings [not charindex]

[mathmath]

Hi everyone,

I am working on a project where i have encounter such problems:

Data samples:
LA-1-2,
LA-1-2-33,
LA-1-22-333-4444,
LA-2-3,
LA-12-34,
LA-123-45-67
..
with upto 8 dashes

Objective:

I need to find the values behind each dashes into multiple columns in orders.
[e.g LA-1-2-33 will have columnA = 1,columnB = 2,columnC=33...etc.].

Since there are alot of situations,i dont know how to query it. [there is a way to do it by using declare & count but very tedious].

*im new to mssql. Please elaborate as much as possible if you have "advance" queries like create functions.

Thanks Very Much! =)

[jbisono]

Hi, i once have that problem and i use this function and it work for me.

http://geekswithblogs.net/AngelEyes/archive/2007/04/12/111504.aspx

[mathmath]

Hi, i once have that problem and i use this function and it work for me.

http://geekswithblogs.net/AngelEyes/archive/2007/04/12/111504.aspx

Here's the function from the website:

CREATE FUNCTION dbo.fnSplit(
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
 
 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
GO

--Test
select * from fnSplit('1,22,333,444,,5555,666', ',')
select * from fnSplit('1##22#333##444','##') --note second item has embedded #
select * from fnSplit('1 22 333 444 5555 666', ' ')

I dont quite understand how it works.
My question is , How can i apply the function for a whole column. and seperate each strings into different columns.

Thanks

[mathmath]

It worked!
I used the following functions.
Hope it helps.
Thanks All!!

Credit to "Visakh16" from Sqlteam forum

just use a function to parse the string and seperate the values b/w - characters. you can try below as an example

SELECT t.yourcol,f.ID,f.Val
FROM yourtable t
CROSS APPLY dbo.ParseValues (REPLACE(t.yourcol,'LA-',''),'-') f

ParseValues can be found below

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=115544

[jbisono]

Good excellent thanks.