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测试通过。今天就写到这,有遇到好用的将继续更新。