SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/*
select tt=dbo.ufnGetMessageFromLine('<tr class=''dbg-1''><td><span>Book: The Powers Below</span></td><td> : </td><td>《深渊之神》</td></tr>','E')
*/
CREATE Function [dbo].[ufnGetMessageFromLine]
(
@strOri nvarchar(1000),
@Type varchar(10)
)
RETURNS nvarchar(1000)
AS
begin
declare @intLen int
declare @Sql nvarchar(1000)
select @intLen=1 --where : locates
DoLoop:
select @intLen =@intLen +1
while substring(@strOri,@intLen,21)<>'</td><td> : </td><td>' and @intLen<len(@strOri)
begin
select @intLen =@intLen +1
end
--if substring(@strOri,1,@intLen-1) not like '%</td><td> ' goto DoLoop
--Return ltrim(rtrim(substring(@strOri,1,@intLen-1)))
if substring(@Type,1,1)='E'
Begin
select @strOri=substring(@strOri,1,@intLen-1)
select @strOri=replace(@strOri,'<tr class=''dbg-1''><td><span>','')
select @strOri=replace(@strOri,'<tr class=''dbg1''><td><span>','')
select @strOri=replace(@strOri,'</span>','')
select @strOri=replace(@strOri,'"','"')
select @strOri=replace(@strOri,''','''')
select @strOri=replace(@strOri,'""','')
Return ltrim(rtrim(@strOri))
end
if substring(@Type,1,1)='C'
Begin
select @strOri=substring(@strOri,@intLen,1000)
select @strOri=replace(@strOri,'</td><td> : </td><td>','')
select @strOri=replace(@strOri,'</td></tr>','')
select @strOri=replace(@strOri,'"','"')
select @strOri=replace(@strOri,''','''')
select @strOri=replace(@strOri,'""','')
Return ltrim(rtrim(@strOri))
end
Return 'NotFind'
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TABLE [dbo].[From1117File_U_Temp] (
[Name] [nvarchar] (1000) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ItemName] (
[PageNo] [int] NULL ,
[EName] [nvarchar] (400) COLLATE Chinese_PRC_Stroke_CI_AS NULL ,
[CName] [nvarchar] (400) COLLATE Chinese_PRC_Stroke_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Live_ItemName] (
[SeqId] [int] IDENTITY (1, 1) NOT NULL ,
[PageNo] [int] NULL ,
[EName] [nvarchar] (400) COLLATE Chinese_PRC_Stroke_CI_AS NULL ,
[CName] [nvarchar] (400) COLLATE Chinese_PRC_Stroke_CI_AS NULL ,
[ItemNo] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Live_ItemName] WITH NOCHECK ADD
CONSTRAINT [PK_Live_ItemName] PRIMARY KEY CLUSTERED
(
[SeqId]
) ON [PRIMARY]
GO
CREATE INDEX [IX_ItemName_Ename] ON [dbo].[ItemName]([EName]) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/*
Design by Jackie Chen 20080612
time during running: 5s
select * from Live_ItemName where ItemNo is not null
*/
CREATE PROCEDURE [dbo].[uspGet_Live_ItemName]
AS
--select * from ItemName a --66994
--select distinct a.Ename from ItemName a --41570
truncate table Live_ItemName
insert Live_ItemName(PageNo,Ename,Cname)
select PageNo=max(PageNo),Ename,cname=(select top 1 cname from ItemName where ename=a.ename order by PageNo desc) from ItemName a group by Ename
order by PageNo,Ename
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/*
Design by Jackie Chen 20080601
time during running: 40s
*/
CREATE PROCEDURE [dbo].[uspTransDataFrom1117File]
AS
declare @intStep int
declare @Sql nvarchar(4000)
declare @SqlA nvarchar(1000)
declare @SqlB nvarchar(1000)
declare @strDir nvarchar(100)
declare @strReturn nvarchar(100)
select @strDir='C:\ConvertZ\'
select @strReturn=char(10)
truncate table ItemName
select @intStep=1,
@SqlA='BULK INSERT dbo.From1117File_U_Temp FROM '''+@strDir,
@SqlB='.html'' WITH (CODEPAGE = ''936'' ,ROWTERMINATOR = '''+@strReturn+''')'
while @intStep<=1117
begin
select @Sql=@SqlA+cast(@intStep as nvarchar(9))+@SqlB
exec( @Sql)
insert [ItemName]
select t=@intStep,dbo.ufnGetMessageFromLine(name,'E'),dbo.ufnGetMessageFromLine(name,'C')
from From1117File_U_Temp where name like '<tr clas�
制作WOW中英文翻译表
需积分: 12 170 浏览量
2008-06-14
13:25:33
上传
评论 1
收藏 148KB 7Z 举报
cdwolfling
- 粉丝: 5
- 资源: 8
最新资源
- C#的Winform通过GDI+以及Datatable绘制基于CSV文件的风羽图
- 三菱PLC例程源码定型机超喂
- 3LN01C-TB-H-VB一款N-Channel沟道SOT23的MOSFET晶体管参数介绍与应用说明
- 13章Go微服务精讲:Go-Zero全流程实战即时通讯
- 3J14-VB一款P-Channel沟道SOT23的MOSFET晶体管参数介绍与应用说明
- 三菱PLC例程源码定时关闭所有的输出点定时停机365按x0复位
- 前端开发中国地图资源China.json
- 真实光伏电站数据集免费下载
- 3B14-VB一款N-Channel沟道SOT23的MOSFET晶体管参数介绍与应用说明
- 三菱PLC例程源码定时程序,程序带注解!!
资源上传下载、课程学习等过程中有任何疑问或建议,欢迎提出宝贵意见哦~我们会及时处理!
点击此处反馈