---将全角符号转换成半角
ALTER FUNCTION ChangeSign ( @fphm NVARCHAR(MAX) )
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @pat NVARCHAR(8) ,
@step INT ,
@i INT ,
@spc INT
SELECT @pat = N'%[!-~]%' ,
@step = -65248 ,
@fphm = REPLACE(@fphm, N' ', N' ')
SET @i = PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN, @fphm)
WHILE @i > 0
SELECT @fphm = REPLACE(@fphm, SUBSTRING(@fphm, @i, 1),
NCHAR(UNICODE(SUBSTRING(@fphm, @i, 1))
+ @step)) ,
@i = PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN, @fphm)
RETURN(@fphm)
END
go
------------------------返回 去掉左右空格---------转换半角后的 值
CREATE FUNCTION ChangeSignStrTrim ( @fphm VARCHAR(1000) )
RETURNS VARCHAR(1000)
AS
BEGIN
SET @fphm = RTRIM(LTRIM(dbo.ChangeSign(@fphm)))
RETURN(@fphm)
END
GO
SELECT dbo.ChangeSignStrTrim(' 你好JHD jjkjd43 ')