SQL SERVER FunctionALTER FUNCTION [dbo].[To_single_byte]
(
@str NVARCHAR(4000) --要转换的字符串
)RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @pat nvarchar(8),@step int,@i int,@spc int 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求oracle functionsql serveroraclefunctionsql
(
@str NVARCHAR(4000) --要转换的字符串
)RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @pat nvarchar(8),@step int,@i int,@spc int 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求oracle functionsql serveroraclefunctionsql
RETURN VARCHAR2
IS
PAT VARCHAR2(20);
lv_str varchar2(4000);
BEGIN
pat := '[ !~ -]'; --这个把括号内的字符串都REPLACE成NULL了,不知道你想要得是什么样的。
SELECT REGEXP_REPLACE(STR,pat,'')
INTO lv_str
FROM DUAL;
RETURN lv_str;
END;--TEST--------------------
declare
s varchar2(4000);
begin
s := func_name('!122 3 dji-djk-d!');
dbms_output.put_line(s);
end;
--OUTPUT------------------
1223djidjkd