--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