 [SQLSERVER]sqlserver查找txt文件中指定格式 |
| ALTER proc input_text@file varchar(200) = 'E:\a12.txt',@format varchar(50) ='%13[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%',@formatlength int = 11as
if exists(select * from tempdb.dbo.sysobjects where name = '##text' and xtype = 'U')begin drop table ##textend
create table ##text(Col001 nvarchar(4000))
declare @start int
declare @cmd varchar(1000)set @cmd = 'bcp ##text in ' + @file + ' -c -P'exec master..xp_cmdshell @cmd
if @format = '%' insert into text(filename,body) select @file,ltrim(rtrim(Col001)) from ##textelsebegin
declare @len int declare @text nvarchar(4000) declare cur_findword cursor scroll for select Col001 as Col001 from ##text open cur_findword fetch next from cur_findword into @text
while @@fetch_status = 0 begin while patindex(@format,@text) != 0 begin set @len = len(@text) insert into text(filename,body) values(@file,substring(@text,patindex(@format,@text),@formatlength)) set @text = substring(@text,patindex(@format,@text)+@formatlength,@len) end
fetch next from cur_findword into @text end close cur_findword DEALLOCATE cur_findwordend | |
|
|
|
