学步园

--1.提取中文
create function dbo.getCN
(
	@str as nvarchar(4000)
)
returns nvarchar(4000)
  as
  begin
  declare  @word  nchar(1),@CN nvarchar(4000)
  set @CN=''
  while len(@str)>0
  begin
  set @word=left(@str,1)
  if unicode(@word) between  19968  and  19968+20901
      set @CN=@CN+@word
  set @str=right(@str,len(@str)-1)
  end
  return @CN
  end
PRINT dbo.getCN('ASDKG论坛KDL')
--论坛
PRINT dbo.getCN('ASDKG論壇KDL')
--論壇
PRINT dbo.getCN('ASDKDL')
--空
--2.提取数字
CREATE FUNCTION DBO.getNumber(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^0-9]%',@S) > 0
BEGIN
set @S=stuff(@S,patindex('%[^0-9]%',@S),1,'')
END
RETURN @S
END
GO
--测试 多个条件用逗号隔开 PATINDEX('%[^0-9,*]%',@S) > 0
PRINT dbo.getNumber('呵呵ABC123ABC')
GO
--123
--3.提取英文
CREATE FUNCTION DBO.getEn(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^a-z]%',@S) > 0
BEGIN
set @S=stuff(@S,patindex('%[^a-z]%',@S),1,'')
END
RETURN @S
END
GO
--测试
PRINT DBO.getEn('呵呵ABC123ABC')
--ABCABC
GO

--4.提取简体中文
CREATE FUNCTION DBO.getSimplifiedCN(@S NVARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^吖-座]%',@S) > 0
SET @S = STUFF(@S,PATINDEX('%[^吖-座]%',@S),1,N'')
RETURN @S
END
GO
PRINT DBO.getSimplifiedCN('呵呵ABC123ABC')
GO
--5.过滤重复字符
CREATE FUNCTION DBO.distinct_str(@S NVARCHAR(100),@SPLIT VARCHAR(50))
RETURNS VARCHAR(100)
AS
BEGIN
IF @S IS NULL RETURN(NULL)
DECLARE @NEW VARCHAR(50),@INDEX INT,@TEMP VARCHAR(50)
IF LEFT(@S,1)<>@SPLIT
SET @S = @SPLIT+@S
IF RIGHT(@S,1)<>@SPLIT
	SET @S = @S+@SPLIT
WHILE CHARINDEX(@SPLIT,@S)>0 AND LEN(@S)<>1
BEGIN
	SET @INDEX = CHARINDEX(@SPLIT,@S)
	SET @TEMP = LEFT(@S,CHARINDEX(@SPLIT,@S,@INDEX+LEN(@SPLIT)))
	IF @NEW IS NULL
		SET @NEW = ISNULL(@NEW,'')+@TEMP
	ELSE
		SET @NEW = ISNULL(@NEW,'')+REPLACE(@TEMP,@SPLIT,'')+@SPLIT
	WHILE CHARINDEX(@TEMP,@S)>0
	BEGIN
		SET @S=STUFF(@S,CHARINDEX(@TEMP,@S)+LEN(@SPLIT),CHARINDEX(@SPLIT,@S,CHARINDEX(@TEMP,@S)+LEN(@SPLIT))-CHARINDEX(@TEMP,@S),'')
	END
END
	RETURN RIGHT(LEFT(@NEW,LEN(@NEW)-1),LEN(LEFT(@NEW,LEN(@NEW)-1))-1)
END
GO
PRINT DBO.distinct_str('A,A,B,C,C,B,C,',',')
--A,B,C
--6.过滤重复字符2
CREATE FUNCTION DBO.distinct_str2(@S varchar(8000))
RETURNS VARCHAR(100)
AS
BEGIN
IF @S IS NULL RETURN(NULL)
DECLARE @NEW VARCHAR(50),@INDEX INT,@TEMP VARCHAR(50)
WHILE LEN(@S)>0
BEGIN
	SET @NEW=ISNULL(@NEW,'')+LEFT(@S,1)
	SET @S=REPLACE(@S,LEFT(@S,1),'')
END
	RETURN @NEW
END
GO
print DBO.distinct_str2('AABCCD')

--ABCD