UDF Sample Code


  

Alter FUNCTION [dbo].[udfCanonical2] (@path VARCHAR(2000))
RETURNS VARCHAR(2000)
AS
BEGIN
 
	
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)  
 
SELECT  @path= (select C + '' from (select N, substring(@path, N, 1) C from Nums 
			
			where N<=datalength(@path)) t
            where PatIndex('%[a-zA-Z0-9 ]%',C)> 0
            order by N
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)') 
       
RETURN  Replace(@path, ' ', '-')

END

 
 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s