sql server function stuff before and after the string

To append  a string at each comma

Example for the string 'A,B,C'.

Result should be 'beforestringAafterstringbeforestringBafterstringbeforestringCafterstring

when you need these operations ?

Mostly during automations , for example if you are retrieving the columns from the sys.columns table to generate a generic select statement with casting as below example

column names output from the syscolumns 'IID,Name,Age'

output : CAST(IID AS VARCHAR(MAX)), CAST(Name AS VARCHAR(MAX)), CAST(Age AS VARCHAR(MAX))

execute the script to create the function as below and run the below statement

select dbo.fn_stuff('xx_,xxxx,',',','before','after')

create  function dbo.fn_stuff(@vString varchar(4000),@exp varchar(5),@vStart varchar(2000),@vEnd varchar(2000)) returns varchar(8000) as begin declare @vRet varchar(8000) SELECT @vRet= coalesce(nullif(cast(charindex(@vString,@exp) as varchar(max)),0), STUFF(replace(@exp+@vString,@exp,@vEnd+@vStart) + @vEnd , 1,len(@vEnd),'')) return @vRet end

sql server function charindex at n position

Charindex at n position

To find the charindex of an expression “_” in n position say 3^rd occurancein a given word 'xx_xxx_xx_xx'

Result : 10

SELECT dbo.fn_charIndex('_',3,' xx_xxx_xx_xx')

CREATE FUNCTION [dbo].[fn_charIndex](@Expression varchar(256),@nPos int,@Word varchar(2000)) returns int as begin Declare @ret int,@strt int, @strLen int set @ret = 0 set @strt = 0 while not @strt = @npos begin select @ret = charindex(@Expression,@Word,@ret+1) set @strt = @strt +1 IF @ret = 0 Break ELSE Continue end return @ret end

sql server function to return the column datatype

select dbo.FN_COL_TYPE ('SALES.CUSTOMER','customerid')

CREATE FUNCTION FN_COL_TYPE (@TBL_NAME VARCHAR(256),@COL_NAME VARCHAR(256))
returns varchar(256)
as
begin
DECLARE @vType VARCHAR(50)

select @vType = b.name from sys.columns a join sys.types b on a.system_type_id = b.system_type_id
where object_id = object_id(@TBL_NAME)
and A.name = @COL_NAME
return @vType
end

SQL Server Function to list the values as comma Separated Values.

This is one of the most common requirement and the most common bottleneck in stored procedure which are using cursors to attain this functionality.
If you are observing poor performance of your queries which are using cursors to produce a comma separated list then replace the code with below , it will improve the performance a lot.

Below function will produce the list of values as a comma seperated string for vname column in the table test.

GO

create table test (iid int, vname varchar(99))

GO

insert into test values (1,'A')
insert into test values (1,'B')
insert into test values (1,'C ')
insert into test values (2,'X')
insert into test values (2,NULL)
insert into test values (2,'Z')
insert into test values (3,NULL)
insert into test values (3,'DD')
GO


create function fn_tbl(@IID INT )
RETURNS TABLE

AS RETURN

(SELECT distinct B.IID, empnos = replace(replace((SELECT vname FROM test A
WHERE A.IID= B.IID ORDER BY A.IID FOR xml PATH ('')) ,'',''),'',',')
FROM TEST B
where B.IID = @IID
)

GO

SELECT distinct b.iid,left(empnos,len(empnos)-1) FROM TEST CROSS APPLY DBO.fn_tbl(iid) b

GO
SELECT * FROM DBO.fn_tbl(1)
------------------------------------------------------------------------------------
------------------------------------------------------------------------------------

GO

Reverse - From comma seperated list to columns

SELECT * FROM DBO.fn_comma ('A,B,C,D,E,-,Z,X,R')



ALTER  function fn_comma(@valueList varchar(MAX))
returns @RET TABLE (STR VARCHAR(MAX))
as
begin
DECLARE @pos INT
DECLARE @len INT
DECLARE @value varchar(MAX)
SET @valueList  = @valueList +','
set @pos = 0
set @len = 0

WHILE CHARINDEX(',', @valueList, @pos+1)>0
BEGIN
    set @len = CHARINDEX(',', @valueList, @pos+1) - @pos
    set @value = SUBSTRING(@valueList, @pos, @len)
    --SELECT @pos, @len, @value /*this is here for debugging*/
       
    INSERT INTO @RET  SELECT @value
   
    set @pos = CHARINDEX(',', @valueList, @pos+@len) +1
END

RETURN
end