August 3, 2009 at 9:36 am
in this table one column has entrys that look like : res1, res2, res3
well i need to split these out to return each on it's own.
[font="Comic Sans MS"]Being "normal" is not necessarily a virtue; it rather denotes a lack of courage.
-Practical Magic[/font]
August 3, 2009 at 2:19 pm
You can do this a few ways but it depends on the data that's being input.
Do you know what kind of data is going to be input? Will it be from a predetermined list of values?
Will they always follow the same format i.e. 4 characters then a comma Res1,Res2,Res3,Res4, ?
Also what is the max amount of values that can be entered for that column?
You also said they need to be output on their own. Does this mean you want them in a row of their own or in a column of their own (like a pivot table)?
August 3, 2009 at 2:53 pm
Here is one way to do it:
declare @Acc varchar(100),@AccString varchar(100)
set @AccString = 'Res1,Res2,Res3,Res4' --or select @AccString = Column from YourTable
--Create Temp Table
IF Object_ID ('#Array') is null
BEGIN
Create Table #Array (Result varchar(8))
END
While len(@AccString) > 0
BEGIN
Set @Acc = left(@AccString,5) --Get first 5 chars
set @AccString = replace(@AccString,@Acc,'') --Remove each result from string
set @Acc = replace(@Acc,',','') --Remove comma from string
--Insert Data
Insert Into #Array
select @Acc
END
select * from #Array
Drop Table #Array
August 3, 2009 at 3:30 pm
the data will be diffrent, it's a list of menu choices. ie:
links, admin, user, database, books
and so on. no limit on how many. from what i understand one column is being used to store these choices, but they want to break them appart to display them as if they came out as diffrent records. I've already suggested just take what comes out and useing the frontend code to break it apart and they can do this, they just want to know if it's easier to do in sql.
[font="Comic Sans MS"]Being "normal" is not necessarily a virtue; it rather denotes a lack of courage.
-Practical Magic[/font]
August 3, 2009 at 3:34 pm
How about posting the table DDL, sample data, and expected results. Please read the first article referenced in my signature block about asking for help. Follow the instruction in that article to get the best tested help quickly.
August 4, 2009 at 7:11 am
If the data is going to be different lengths then the code I provided would not work although it can be modified to do so.
As far as the function Lynn posted I believe that won't work for SQL 2000 which this topic is under (SQL 7,2000).
What version of SQL are you running?
August 4, 2009 at 10:37 am
AVB (8/4/2009)
If the data is going to be different lengths then the code I provided would not work although it can be modified to do so.As far as the function Lynn posted I believe that won't work for SQL 2000 which this topic is under (SQL 7,2000).
What version of SQL are you running?
But the code in the following article will work... and it contains no explicit loops so it's also very fast.
http://qa.sqlservercentral.com/articles/T-SQL/62867/
There's a subsection near the end of the article that shows how to very quickly and easily split an entire table's worth of CSV's in a single, very simple SELECT.
--Jeff Moden
August 4, 2009 at 11:53 am
Jeff,
Good article. I'll have to try that next opportunity I get!
August 4, 2009 at 2:17 pm
AVB (8/4/2009)
Jeff,Good article. I'll have to try that next opportunity I get!
Thanks for the feedback, AVB.
--Jeff Moden
August 4, 2009 at 2:18 pm
purplebirky (8/3/2009)
in this table one column has entrys that look like : res1, res2, res3well i need to split these out to return each on it's own.
purplebirky,
See the article I posted above.
--Jeff Moden
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply