create function func_RemoveRepeat(@str varchar(100)) returns varchar(100) as begin --declare @str varchar(30)='1231abc' declare @ch varchar(1)='' declare @strs varchar(30)='' while(LEN(@str)>0) begin set @ch=left(@str,1) set @strs=@strs+@ch set @str=REPLACE(@str,@ch,'') end return @strs endselect dbo.func_RemoveRepeat('1232345abcb')
--只能建个自定义函数处理下了 CREATE FUNCTION MyReplace(@STR VARCHAR(1000)) RETURNS VARCHAR(200) AS BEGIN DECLARE @RETURNSTR VARCHAR(200) SET @RETURNSTR='' DECLARE @CHAR CHAR(1) WHILE LEN(@STR)>0 BEGIN SET @CHAR=SUBSTRING(@STR,1,1) SET @STR=REPLACE(@STR,@CHAR,'') SET @RETURNSTR=@RETURNSTR+@CHAR END RETURN @RETURNSTR END GO SELECT DBO.MyReplace('123321452546') --123456
WITH a1 (cstr) AS (select '123321452546') ,a2 AS ( SELECT SUBSTRING(cstr,1,1) c,cstr,1 n FROM a1 UNION ALL SELECT SUBSTRING(cstr,n+1,1) c,cstr,n+1 FROM a2 WHERE n+1<=LEN(cstr) ) ,a3 AS (SELECT DISTINCT c FROM a2) SELECT REPLACE((SELECT c+',' FROM a3 FOR XML PATH('')),',','')
纯数字可以这样做,没什么好借鉴的~ ;with cte as( select 0 as n union all select n+1 from cte where n<100 ), cc as ( select '11532221165159983s72141' as inputStr ) select cast(n as varchar)+'' from cte as a where exists(select 1 from cc where charindex(cast(n as varchar),inputStr)>0 and len(n)=1) for xml path('')
create function func_RemoveRepeat(@str varchar(100))
returns varchar(100)
as
begin
--declare @str varchar(30)='1231abc'
declare @ch varchar(1)=''
declare @strs varchar(30)=''
while(LEN(@str)>0)
begin
set @ch=left(@str,1)
set @strs=@strs+@ch
set @str=REPLACE(@str,@ch,'')
end
return @strs
endselect dbo.func_RemoveRepeat('1232345abcb')
CREATE FUNCTION MyReplace(@STR VARCHAR(1000))
RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @RETURNSTR VARCHAR(200)
SET @RETURNSTR=''
DECLARE @CHAR CHAR(1)
WHILE LEN(@STR)>0
BEGIN
SET @CHAR=SUBSTRING(@STR,1,1)
SET @STR=REPLACE(@STR,@CHAR,'')
SET @RETURNSTR=@RETURNSTR+@CHAR
END
RETURN @RETURNSTR
END
GO
SELECT DBO.MyReplace('123321452546')
--123456
WITH a1 (cstr) AS
(select '123321452546')
,a2 AS
(
SELECT SUBSTRING(cstr,1,1) c,cstr,1 n FROM a1
UNION ALL
SELECT SUBSTRING(cstr,n+1,1) c,cstr,n+1 FROM a2
WHERE n+1<=LEN(cstr)
)
,a3 AS
(SELECT DISTINCT c FROM a2)
SELECT REPLACE((SELECT c+',' FROM a3 FOR XML PATH('')),',','')
;with cte as(
select 0 as n
union all
select n+1 from cte where n<100
),
cc as (
select '11532221165159983s72141' as inputStr
)
select cast(n as varchar)+'' from cte as a
where exists(select 1 from cc
where charindex(cast(n as varchar),inputStr)>0 and len(n)=1)
for xml path('')
--请教下这两种写法?05+才有的么?没用过declare @ch varchar(1)=''
declare @strs varchar(30)=''
--请教下这两种写法?05+才有的么?没用过declare @ch varchar(1)=''
declare @strs varchar(30)=''
08才支持