a challenge? - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

a challenge?

Author

Topic

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-10 : 03:42:55

i've got one for you guys...

declare @table table(field1 as nvarchar(50))
declare @string nvarchar(50)

set @string='string1,string2'

how do you insert the values of @string (string1 and string2) to @table without parsing?

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-09-10 : 03:56:17

maybe you need to explain what you mean. To my mind what you have just said equates to something like
"how do separate two values which have been combined into one value, without separating them?"

Can you rephrase?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-10 : 04:06:26

lolz, i knew it sounded odd...

i already have a solution but i think it's too long...
separate the two values and insert into a table datatype (@t2) then do an "insert into @t1(f1) select f2 from @t2"

i was thinking if there's a shorter way of doing this like a one liner maybe using some built in function? if there's some way to replace the "select" to directly access the string values.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-09-10 : 04:08:48

must just be because its friday, but I have absolutely no idea what you just said. how about example data and results?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-10 : 04:15:47

Is this what you mean?

insert @table (field1)

select substring(@string,1,charindex(',',@string) - 1)
union
select substring(@string,charindex(',',@string) + 1,50)

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-10 : 04:17:54

it is friday, or else i won't be asking this

quote:

declare @table table(field1 as nvarchar(50))
declare @string nvarchar(50)

set @string='string1,string2'

i want string1 and string2 in @table(field1), so i'll get 2 rows.

is there a way to insert the values 'string1' and 'string2' into @table without passing the parsed string to another variable?

i'm looking for a function similar to "for each" or "split"

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-10 : 04:19:24

Thanks ken,
but in this instance, i need to include more unions if i have more than 2 values right?

but this is close, just need to think of way how to minimize the unions though

quote:
Originally posted by kselvia

Is this what you mean?

insert @table (field1)

select substring(@string,1,charindex(',',@string) - 1)
union
select substring(@string,charindex(',',@string) + 1,50)

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-10 : 07:30:25

How about:


declare @table table(field1 nvarchar(50))
declare @string nvarchar(50)

set @string='string1,string2'

Insert Into @table
Select data From dbo.split(@string,',')

Select * From @table

By the way split is a nice function to keep around...


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

Corey

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-09-10 : 07:45:15

Here is another method: http://www.sqlteam.com/item.asp?ItemID=2652

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources