如果是sql2005借助clr集成的功能,写起来比较简单。2000下抄个老大写的吧。CREATE FUNCTION f_Convert(
@str NVARCHAR(4000), --要转换的字符串
@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
GO
--邹老大写的函数
DECLARE @t TABLE(col nvarchar(10))
INSERT @t SELECT 'aa'
UNION ALL SELECT 'Aa'
UNION ALL SELECT 'AA'
UNION ALL SELECT 'A,A'
UNION ALL SELECT 'A,A'
select * from @t
select dbo.f_Convert(col,0) from @t
GODROP FUNCTION dbo.f_Convert
go
@str NVARCHAR(4000), --要转换的字符串
@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
GO
--邹老大写的函数
DECLARE @t TABLE(col nvarchar(10))
INSERT @t SELECT 'aa'
UNION ALL SELECT 'Aa'
UNION ALL SELECT 'AA'
UNION ALL SELECT 'A,A'
UNION ALL SELECT 'A,A'
select * from @t
select dbo.f_Convert(col,0) from @t
GODROP FUNCTION dbo.f_Convert
go
然后要选连接,连到你的实例及库》在项目上右击》新建函数》输入名为 CharConvert然后 CharConvert.cs代码如下:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction] public static SqlString CharConvert(string source)
{
// 在此处放置代码 StringBuilder result = new System.Text.StringBuilder(source.Length,source.Length);
for(int i=0; i<source.Length; i++)
{
if(source[i] >= 65281 && source[i] <= 65373)
{
result.Append((char)(source[i] - 65248));
}
else if(source[i] == 12288)
{
result.Append(" ");
}
else
{
result.Append(source[i]);
}
}
return result.ToString();
}
};
编译生成后,点部署,就会部署到你刚进来时指定的库下。在库的》可编程性》函数》标量函数》中可以找到。然后运行就可以看效果
DECLARE @t TABLE(col nvarchar(10))
INSERT @t SELECT 'aa'
UNION ALL SELECT 'Aa'
UNION ALL SELECT 'AA'
UNION ALL SELECT 'A,A'
UNION ALL SELECT 'A,A'
SELECT dbo.CharConvert(col) FROM @t/*
aa
Aa
AA
A,A
A,A
*/
CREATE FUNCTION f_Convert(
@str NVARCHAR(4000), --要转换的字符串
@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
GO
@str NVARCHAR(4000), --要转换的字符串
@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