SqlServer 小记 PATINDEX

1.declare @str nvarchar(max)declare @str1 nvarchar(50)declare @str2 nvarchar(50)select @str='XX'Declare @n int set @n=18869while @n<=20000begin   select @str1='<'+convert(nvarchar(10),@n)+'>' select @str2='<'+convert(nvarchar(10),@n)+'title'  select @str=replace(@str,@str1,'') select @str=replace(@str,@str2,'') select @str=replace(@str,'<title','') select @str=replace(@str,'=','$') set @n=@n+1EndPRint @strselect @str2.declare @str nvarchar(max)select @STR='XX'select REPLACE(@str,'>','$')3.DECLARE @SourceSql VARCHAR(MAX)DECLARE @StrSeprate VARCHAR(10)    DECLARE @i INT    DECLARE @Name NVARCHAR(50)    DECLARE @j INT        SET @StrSeprate='$'    SET @SourceSql='XXX'    SET @SourceSql = RTRIM(LTRIM(@SourceSql))    SET @i = CHARINDEX(@StrSeprate,@SourceSql)    WHILE @i>=1 BEGINSET @Name = LEFT(@SourceSql,@i-1)        SET @SourceSql = SUBSTRING(@SourceSql, @i + 1, LEN(@SourceSql) - @i)        SET @i = CHARINDEX(@StrSeprate,@SourceSql)                SET @j= PATINDEX('%[0-9]%',@Name)        if @j=0        BEGINIF NOT EXISTS(SELECT * FROM c_all WHERE dname=@Name)BEGININSERT INTO c_all(dname) VALUES(@Name)END        END        ELSE        BEGIN        IF NOT EXISTS(SELECT * FROM c_all WHERE uname=@Name)BEGININSERT INTO c_all(uname) VALUES(@Name)END        END            END