收集一些非常好用常用的用户函数。分享给大家参考

收集一些非常好用常用的用户函数。分享给大家参考。 view sourceprint? --可将字符串转换为全角或半角的 CREATE FUNCTION [dbo].[f_Convert] ( @str NVARCHAR(4000)=N'' , --要转换的字符串 @flag BIT=0 --转换标志,0转换成半角,1转换成全角 ) RETURNS NVARCHAR(4000) AS BEGIN DECLARE @pat NVARCHAR(8) , @step INT , @i INT , @spc INT IF @flag = 0 SELECT @pat = N'%[！-～]%', @step = -65248, @str = REPLACE(@str, N'　', N' ') ELSE SELECT @pat = N'%[!-~]%', @step = 65248, @str = REPLACE(@str, N' ', N'　') SET @i = PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN, @str) WHILE @i > 0 SELECT @str = REPLACE(@str, SUBSTRING(@str, @i, 1), NCHAR(UNICODE(SUBSTRING(@str, @i, 1)) + @step)), @i = PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN, @str) RETURN(@str) END GO --获取每个汉字首个拼音 CREATE FUNCTION [dbo].[f_GetPy] ( @str NVARCHAR(4000)=N'' ) RETURNS NVARCHAR(4000) AS BEGIN DECLARE @strlen INT , @re NVARCHAR(4000) DECLARE @t TABLE ( chr NCHAR(1) COLLATE Chinese_PRC_CI_AS , letter NCHAR(1) ) INSERT INTO @t ( chr, letter ) SELECT '吖', 'A' UNION ALL SELECT '八', 'B' UNION ALL SELECT '嚓', 'C' UNION ALL SELECT '咑', 'D' UNION ALL SELECT '妸', 'E' UNION ALL SELECT '发', 'F' UNION ALL SELECT '旮', 'G' UNION ALL SELECT '铪', 'H' UNION ALL SELECT '丌', 'J' UNION ALL SELECT '咔', 'K' UNION ALL SELECT '垃', 'L' UNION ALL SELECT '嘸', 'M' UNION ALL SELECT '拏', 'N' UNION ALL SELECT '噢', 'O' UNION ALL SELECT '妑', 'P' UNION ALL SELECT '七', 'Q' UNION ALL SELECT '呥', 'R' UNION ALL SELECT '仨', 'S' UNION ALL SELECT '他', 'T' UNION ALL SELECT '屲', 'W' UNION ALL SELECT '夕', 'X' UNION ALL SELECT '丫', 'Y' UNION ALL SELECT '帀', 'Z' SELECT @strlen = LEN(@str), @re = '' WHILE @strlen > 0 BEGIN SELECT TOP 1 @re = letter + @re, @strlen = @strlen - 1 FROM @t a WHERE chr <= SUBSTRING(@str, @strlen, 1) ORDER BY chr DESC IF @@rowcount = 0 SELECT @re = SUBSTRING(@str, @strlen, 1) + @re, @strlen = @strlen - 1 END RETURN(@re) END GO --获取表所有字段 CREATE FUNCTION F_GetTbField ( @Tbname NVARCHAR(100) = '' , @Flag BIT = 0 --0，默认取所有字段，1，除去增量字段 ) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @FieldName VARCHAR(MAX) SELECT @FieldName = ISNULL(@FieldName + ',', '') + NAME FROM sys.columns WHERE object_id = OBJECT_ID(@Tbname, 'U') AND is_identity = CASE @Flag WHEN 0 THEN is_identity ELSE 0 END --RETURN('insert into '+@Tbname+'('+@FieldName+')') RETURN(@FieldName) END GO 以上均在SQL 2008测试通过。今天就写到这，有遇到好用的将继续更新。