STUFF2 - 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

General SQL Server Forums

Script Library

STUFF2

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-10-04 : 03:12:21

[code]CREATE FUNCTION [dbo].[STUFF2]
(
@String nvarchar(max),
@Location nvarchar(max),
@Occurance int,
@Delimeter nvarchar(max)
)
RETURNS nvarchar(max)
BEGIN
DECLARE @Output nvarchar(max) = ''

SELECT @Output = CASE WHEN Data > LEN(@String) THEN
@Output + @Delimeter
ELSE
STUFF(CASE WHEN RN = 1 THEN @String ELSE @Output END, Data + RN - 1, @Occurance, @Delimeter)
END
FROM (
SELECT Data = CAST(Data AS INT) + CASE WHEN @Occurance = 0 THEN 1 ELSE 0 END,
RN = ROW_NUMBER() OVER (ORDER BY LEN(Data), Data)
FROM dbo.fnParseList(',', @Location)
)Src
ORDER BY RN

RETURN @Output
END
[/code]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-10-04 : 03:44:52

Hi Waterduck,

please give us a few words on
when/why to use
how to use
etc.

Thx
Webfred

Too old to Rock'n'Roll too young to die.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-10-04 : 04:43:31

Sorry, forgot the example.

It meant for insert character/string into multiple location.


SELECT
       Original = 'abcdefghijlkmnop',
       Modified = dbo.STUFF2('abcdefghijlkmnop', '6,3, 9, 16', 0, ':')

before - abcdefghijlkmnop
after - abc:def:ghi:jlkmnop:

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-10-04 : 04:44:31

any better idea are highly welcome with a beer =P

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-10-07 : 21:33:20

links for fnParseList
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources