Tuesday, March 24, 2009

UDF function: STRINS

STRINS

Inserts set of characters into another set of characters at a specified starting point.


Syntax
StrIns ( character_expression, start, character_expression )


Arguments
character_expression - an expression of character data. character_expression can be a constant, variable, or column of character data.

start - an integer value that specifies the location to begin insertion. If start or length is negative, a null string is returned. If start is longer than the first character_expression, a null string is returned.


Return Types
nvarchar


The function's text:
CREATE FUNCTION StrIns
( @str_1 nvarchar(4000),
@start int,
@str_2 nvarchar(4000) )
RETURNS nvarchar(4000)
AS
BEGIN
RETURN (STUFF (@str_1, @start, 0, @str_2))
END
GO


Examples
This example returns a character string created by inserting the second string starting at position 2 (at b) into the first string.

SELECT dbo.StrIns('abcdef', 2, 'ijklmn')

Here is the result set:

------------
aijklmnbcdef

(1 row(s) affected)

No comments: