SqlServer 小记 PATINDEX

来源：互联网发布：陆金所网络投融资平台编辑：程序博客网时间：2024/05/22 08:05

1.
declare @str nvarchar(max)
declare @str1 nvarchar(50)
declare @str2 nvarchar(50)
select @str='XX'
Declare @n int
set @n=18869
while @n<=20000
begin

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+1
End
print @str

select @str

2.
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 BEGIN
SET @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
BEGIN
IF NOT EXISTS(SELECT * FROM c_all WHERE dname=@Name)
BEGIN
INSERT INTO c_all(dname) VALUES(@Name)
END
END
ELSE
BEGIN
IF NOT EXISTS(SELECT * FROM c_all WHERE uname=@Name)
BEGIN
INSERT INTO c_all(uname) VALUES(@Name)
END
END

END

0 0