Breaking String to Normalized Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Breaking String to Normalized Table

Author

Topic

ConradK
Posting Yak Master

140 Posts

Posted - 2010-09-12 : 13:22:29

Okay, so I've got data like this.
Parent,Options

'Product one', 'atrb1,atrb2,atrb3'
'product two, 'atrba,atrbb,atrbc'

in a table. I need to break it down into

Product one, atrb1
Product one, atrb2
Product one, atrb3
product two, atrba
product two, atrbb
product two, atrbc

Ive already followed this link: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

it was a bit over my head. I've created functions before, but when I tried this function:
CREATE FUNCTION dbo.Split
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End

Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))

Return
END

-----
and apply it to this:

select dbo.Split(Options,',') from teststrings

I got :
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.Split", or the name is ambiguous.

I'm a little lost here. Help?

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-09-12 : 14:22:34

since you function is table valued function this means that function itself must be selected from table results of function. so you must use it like this:

select * from dbo.Split('somestring;morestrings;evenmorestrings;',';')

to return splited values in a table.

ConradK
Posting Yak Master

140 Posts

Posted - 2010-09-12 : 14:36:06

GENIUS!!!!!

So, obviously this isn't MY function, I never built it... and I'm quite the noob here.

This function... cannot be used to do what I'm looking for here?

ConradK
Posting Yak Master

140 Posts

Posted - 2010-09-12 : 14:38:19

somehow... the key I feel is in this, and then make some dynamic sql and a temp table?

select *
, LEN(Options)-LEN(replace(Options,',','')) as optcount
,CHARINDEX(',',Options,1) as "FirstOptionDelim"
,left(Options,CHARINDEX(',',Options,1)-1) as "OptionApplied"
from teststrings

... damnit, I'm going to have to create my own function here arn't i... such WORK!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources