CREATE FUNCTION f_Convert
(
@str NVARCHAR(max) , --要转换的字符串
@flag BIT --转换标志,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
--调用。
SELECT id,dbo.f_Convert(NAME, 1) AS 'Name' FROM dbo.T_User
id Name
2 李,四
3 王,五
4 赵六
5 qq
6 QQ