April 15, 2008 at 10:23 am
Hi I need to split the string which looks like
n.col1 , b.col1 , n.col2 , b.col2, n.col3 , b.col3 , b.col4 , n.col4 , b.col5,n.col5
n.col1,n.col2 are columns
these are present in @columns
select @columns gives this string
the length of @column is not fixed........we may have n number of columns seperated by ',' but we need to display only first 6 out of them
need to display only first 6 columns out of these.........
n.col1 , b.col1 , n.col2 , b.col2, n.col3 , b.col3
can any one plz help me out in this...............
April 15, 2008 at 10:30 am
Please look at:
http://www.sqlservercentral.com/Forums/Topic457395-338-1.aspx
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 17, 2008 at 2:46 pm
IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[StringToTable]'))
DROP FUNCTION [dbo].[fnDStringToTable]
GO
--This UDF will split a delimited list into a single column table.
CREATE FUNCTION dbo.StringToTable
(
@list NVARCHAR(4000)
, @delimiter NCHAR(1) = ','
)
RETURNS
@tableList TABLE(
value NVARCHAR(100)
)
AS
BEGIN
DECLARE @value NVARCHAR(100)
DECLARE @position INT
SET @list = LTRIM(RTRIM(@list))+ ','
SET @position = CHARINDEX(@delimiter, @list, 1)
IF REPLACE(@list, @delimiter, '') <> ''
BEGIN
WHILE @position > 0
BEGIN
SET @value = LTRIM(RTRIM(LEFT(@list, @position - 1)))
IF @value <> ''
BEGIN
INSERT INTO @tableList (value)
VALUES (@value)
END
SET @list = RIGHT(@list, LEN(@list) - @position)
SET @position = CHARINDEX(@delimiter, @list, 1)
END
END
RETURN
END
GO
April 17, 2008 at 3:11 pm
Yep. That'll do it. (I like the ones that use Numbers tables better, but that one will certainly work.)
Then, select the first six from that function, and you have what you need. "Select top 6 value..."
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 17, 2008 at 10:54 pm
I like the numbers table based solution better, too! It eliminates the loop, runs faster because it uses a single insert, and is a bit easier on the eyes.
See the following for how to make a numbers table. It a very useful tool and every database should have one... very short script...
http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/
Then, you're function would look like this...
CREATE FUNCTION dbo.StringToTable
--===== Define the input parameters
(@SomeCsv NVARCHAR(4000),
@Delim NCHAR(1) = ',')
--===== Define the return table
RETURNS @SplitTable TABLE
(Element INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Value NVARCHAR(4000))
AS
BEGIN
--===== Wrap the input in leading and trailing delimiters
-- This makes all elements look the same and handles
-- blanks, too!
SET @SomeCsv = @Delim+@SomeCsv+@Delim
--===== Do the split and insert as a single set based query.
-- The cross join on the Tally table makes the "set-based loop"
INSERT INTO @SplitTable
(Value)
SELECT Value = SUBSTRING(@SomeCsv, t.N+1, CHARINDEX(@Delim, @SomeCsv, t.N+1)-t.N-1)
FROM dbo.Tally t WITH (NOLOCK)
WHERE SUBSTRING(@SomeCsv, t.N, 1) = @Delim
AND t.N < LEN(@SomeCsv)
RETURN
END
Then, your code to find the first six items would look like this...
SELECT Value
FROM dbo.StringToTable ('A,B,C,D,E,G,H,H,I,J',',')
WHERE Element <= 6
Because of the "element" number column, you can pick any element(s) you want...
SELECT Value
FROM dbo.StringToTable ('A,B,C,D,E,G,H,H,I,J',',')
WHERE Element IN (1,5,6)
--Jeff Moden
Change is inevitable... Change for the better is not.
April 18, 2008 at 8:08 am
yes....i guess this is the best solution
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply