inserting Commas
-
hai guys,
say suppose i have a string like ABC
my function have to return A,B,C
in the same way if my string is A
no need of any commas it needs to return A
the input string may be variable length. it may be of any length.
how we write code for this in tsql.
-
Can you post some sample data? that might help determine if this can be done.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
-
if i call inesrtcomma(AW) it needs to return A,W
insertcomma(BWI) it needs to return B,W,I
insertcomma(w) it needs to return W
insertComma(AWBI) it needs to return A,W,B,I
Thanks
-
Try this
CREATE FUNCTION dbo.InsertCommas(@s VARCHAR(100))RETURNS VARCHAR(100)
AS
BEGIN
SELECT @s-2=STUFF(@s,Number,0,',')
FROM master.dbo.spt_values
WHERE Number BETWEEN 2 AND LEN(@s) AND type='P'
ORDER BY Number DESC
RETURN @s-2
END
GO
DECLARE @s-2 VARCHAR(100)
SET @s-2='W'
SELECT @s-2,dbo.InsertCommas(@s)
SET @s-2='ABWI'
SELECT @s-2,dbo.InsertCommas(@s)
SET @s-2='AW'
SELECT @s-2,dbo.InsertCommas(@s)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Is it always after 1 char that you need the comma. for exmaple would it ever be
A, BC,D
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
-
Now for a really important question, can you show us what you have done to try and solve this problem?
-
By the way, I have an answer to your question, I'm just waiting to see what you have done to solve the problem first.
-
this is what i tried
Declare @ss varchar(100), @idx int
declare @tlen int, @tss varchar(100)
Set @ss = 'kishore'
set @idx = 1
set @tss = ''
select @tlen = len(@ss)
While (@tlen+1 > @idx )
Begin
print ' i am in loop'
select @tss = @tss + substring(@ss,@idx,1)+','
set @idx = @idx + 1
End
select @tss
-
Thankyou Guys.
-
Here is my solution to your problem. I have to upload as a text file as I seem to be having issues with posting the code.
-
Thankyou
Mark
AND
Lynn Pettis
-
sql.kishore1 (5/5/2010)
ThankyouMark
AND
Lynn Pettis
Your welcome. I'm leaving as an exercise to you turn the code into an inline table valued function (it will perform much better than a scalar function).
-
Mark-101232 (5/5/2010)
Try thisCREATE FUNCTION dbo.InsertCommas(@s VARCHAR(100))RETURNS VARCHAR(100)
AS
BEGIN
SELECT @s-2=STUFF(@s,Number,0,',')
FROM master.dbo.spt_values
WHERE Number BETWEEN 2 AND LEN(@s) AND type='P'
ORDER BY Number DESC
RETURN @s-2
END
Beautiful. I like this. Would probably be a little cleaner with a 'genuine' tally table, but this is a very clever use of an existing table with a set of sequential numbers. Well done.
Rob Schripsema
Propack, Inc. -
Mark-101232 (5/5/2010)
Try thisCREATE FUNCTION dbo.InsertCommas(@s VARCHAR(100))RETURNS VARCHAR(100)
AS
BEGIN
SELECT @s-2=STUFF(@s,Number,0,',')
FROM master.dbo.spt_values
WHERE Number BETWEEN 2 AND LEN(@s) AND type='P'
ORDER BY Number DESC
RETURN @s-2
END
GO
DECLARE @s-2 VARCHAR(100)
SET @s-2='W'
SELECT @s-2,dbo.InsertCommas(@s)
SET @s-2='ABWI'
SELECT @s-2,dbo.InsertCommas(@s)
SET @s-2='AW'
SELECT @s-2,dbo.InsertCommas(@s)
Mark, excellent solution! Super!
-
Mark-101232 (5/5/2010)
Try thisCREATE FUNCTION dbo.InsertCommas(@s VARCHAR(100))RETURNS VARCHAR(100)
AS
BEGIN
SELECT @s-2=STUFF(@s,Number,0,',')
FROM master.dbo.spt_values
WHERE Number BETWEEN 2 AND LEN(@s) AND type='P'
ORDER BY Number DESC
RETURN @s-2
END
GO
DECLARE @s-2 VARCHAR(100)
SET @s-2='W'
SELECT @s-2,dbo.InsertCommas(@s)
SET @s-2='ABWI'
SELECT @s-2,dbo.InsertCommas(@s)
SET @s-2='AW'
SELECT @s-2,dbo.InsertCommas(@s)
I love it when people think outside the box. Well done, Mark... Not only for using a Tally method, but for also knowing how to use the spt_Values table.
Let's take it just one step further performance wise. First, a different slant on the function. Let's use XML concatenation instead of using STUFF replacement so that instead of a scalar function, we now have an INLINE TABLE VALUED FUNCTION or "iTVF"...
CREATE FUNCTION dbo.XMLInsertCommas(@String VARCHAR(8000))RETURNS TABLE
AS
RETURN
SELECT STUFF( --==== STUFF gets rid of the first comma (JBM)
( --=== Split and reassemble with commas
SELECT ',' + SUBSTRING(@String,t.N,1)
FROM dbo.Tally t
WHERE t.N BETWEEN 1 AND LEN(@String)
FOR XML PATH('')
)
,1,1,'') AS CommaString
Next... we need some test rows... a hundred thousand should do nicely...
--===== Conditionally drop, recreate, and populate
-- a test table to make reruns easy.
-- This is NOT a part of the solution.
IF OBJECT_ID('TempDB..#Test','U') IS NOT NULL
DROP TABLE #Test
;
SELECT TOP 100000
REPLACE(NEWID(),'-','') AS Characters
INTO #Test
FROM Master.sys.All_Columns ac1
CROSS JOIN Master.sys.All_Columns ac2
;
Now, let's test the two Tally table solutions. We take the display time out of the picture using a "Bit Bucket" variable in the code below. It's important to remove the display time from the picture for tests such as these because it's the "Great Equalizer" and will make different performance code look as if they took nearly the same time to run...
--===== Now, let's take the display time out of the picture-- using the @Bitbucket variable and test both functions.
DECLARE @Bitbucket VARCHAR(100);
SET STATISTICS TIME ON;
PRINT '==================== Original Tally Function ==================== '
SELECT @Bitbucket = dbo.InsertCommas(Characters) FROM #Test ;
PRINT '==================== Reworked Tally Function ==================== '
SELECT @Bitbucket = CommaString FROM #Test CROSS APPLY dbo.XMLInsertCommas(Characters) ic;
SET STATISTICS TIME OFF;
Here's what I get on my 8 year old desktop box for performance results...
==================== Original Tally Function ====================SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 19141 ms, elapsed time = 27869 ms.
==================== Reworked XML Tally Function ====================
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 7718 ms, elapsed time = 8087 ms.
Ya just gotta love iTVF's and Cross Apply even if the iTVF only returns just one value. Thank you, Paul White.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply