27,579
社区成员
发帖
与我相关
我的任务
分享
select substring(c,1,10) as 登记号,
substring(c,12,10) as 处方号,
substring(c,23,11) as 单号,
substring(c,35,9) as 流水号,
substring(c,45,10) as 项目名称,
substring(c,56,6) as 单价,
substring(c,63,3) as 数量,
substring(c,67,3) as 人员,
substring(c,71,2) as 经办人,
substring(c,74,1) as 单位,
substring(c,76,5) as 规格
from tmp
/*
登记号 处方号 单号 流水号 项目名称 单价 数量 人员 经办人 单位 规格
---------- ---------- ----------- --------- ---------- ------ ---- ---- ---- ---- -----
0000004741 1000400821 81100390861 100016179 0.9%氯化钠注射液 5.3000 1.0 陶先明 -1 瓶 500ml
0000004742 1000400822 81100390862 100016175 0.9%氯化钠注射液 5.3000 1.0 陶先明 -1 瓶 500ml
0000004743 1000400823 81100390863 100016175 0.9%氯化钠注射液 5.3000 1.0 陶先明 -1 瓶 500ml
0000004744 1000400824 81100390865 100016174 0.9%氯化钠注射液 5.3000 1.0 陶先明 -1 瓶 500ml
0000004745 1000400825 81100390866 100016173 0.9%氯化钠注射液 5.3000 1.0 陶先明 -1 瓶 500ml
0000004746 1000400826 81100390867 100016172 0.9%氯化钠注射液 5.3000 1.0 陶先明 -1 瓶 500ml
0000004747 1000400827 81100390868 100016171 0.9%氯化钠注射液 5.3000 1.0 陶先明 -1 瓶 500ml
*/
ALTER function [dbo].[splitstring](@c nvarchar(1000))
returns @split table(c1 nvarchar(12),c2 nvarchar(12),C3 nvarchar(12),c4 nvarchar(12),c5 nvarchar(15),c6 nvarchar(10),c7 nvarchar(10),c8 nvarchar(10),c9 nvarchar(10),c10 nvarchar(10),c11 varchar(10))
as
begin
insert into @split(c1) values(substring(@c,1,charindex('|',@c)-1))
set @c=right(@c,len(@c)-charindex('|',@c))
update @split set c2=substring(@c,1,charindex('|',@c)-1)
set @c=right(@c,len(@c)-charindex('|',@c))
update @split set c3=substring(@c,1,charindex('|',@c)-1)
set @c=right(@c,len(@c)-charindex('|',@c))
update @split set c4=substring(@c,1,charindex('|',@c)-1)
set @c=right(@c,len(@c)-charindex('|',@c))
update @split set c5=substring(@c,1,charindex('|',@c)-1)
set @c=right(@c,len(@c)-charindex('|',@c))
update @split set c6=substring(@c,1,charindex('|',@c)-1)
set @c=right(@c,len(@c)-charindex('|',@c))
update @split set c7=substring(@c,1,charindex('|',@c)-1)
set @c=right(@c,len(@c)-charindex('|',@c))
update @split set c8=substring(@c,1,charindex('|',@c)-1)
set @c=right(@c,len(@c)-charindex('|',@c))
update @split set c9=substring(@c,1,charindex('|',@c)-1)
set @c=right(@c,len(@c)-charindex('|',@c))
update @split set c10=substring(@c,1,charindex('|',@c)-1)
set @c=right(@c,len(@c)-charindex('|',@c))
update @split set c11=substring(@c,1,charindex('|',@c)-1)
return
end
go
create table tmp(日期 datetime,c nvarchar(1000))
insert into tmp select '2008-10-1','0000004741|1000400821|81100390861|100016179|0.9%氯化钠注射液|5.3000|1.0|陶先明|-1|瓶|500ml|'
insert into tmp select '2008-10-1','0000004742|1000400822|81100390862|100016175|0.9%氯化钠注射液|5.3000|1.0|陶先明|-1|瓶|500ml|'
insert into tmp select '2008-10-1','0000004743|1000400823|81100390863|100016175|0.9%氯化钠注射液|5.3000|1.0|陶先明|-1|瓶|500ml|'
insert into tmp select '2008-10-1','0000004744|1000400824|81100390865|100016174|0.9%氯化钠注射液|5.3000|1.0|陶先明|-1|瓶|500ml|'
insert into tmp select '2008-10-1','0000004745|1000400825|81100390866|100016173|0.9%氯化钠注射液|5.3000|1.0|陶先明|-1|瓶|500ml|'
insert into tmp select '2008-10-1','0000004746|1000400826|81100390867|100016172|0.9%氯化钠注射液|5.3000|1.0|陶先明|-1|瓶|500ml|'
insert into tmp select '2008-10-1','0000004747|1000400827|81100390868|100016171|0.9%氯化钠注射液|5.3000|1.0|陶先明|-1|瓶|500ml|'
go
create function splitstring(@c nvarchar(1000))
returns @split table(c1 nvarchar(12),c2 nvarchar(12),C3 nvarchar(12),c4 nvarchar(12),c5 nvarchar(15),c6 nvarchar(10),c7 nvarchar(10),c8 nvarchar(10),c9 nvarchar(10),c10 nvarchar(10),c11 varchar(10))
as
begin
insert into @split(c1) values(substring(@c,1,charindex('|',@c)-1))
set @c=right(@c,len(@c)-charindex('|',@c)+1)
update @split set c2=substring(@c,1,charindex('|',@c)-1)
set @c=right(@c,len(@c)-charindex('|',@c)+1)
update @split set c3=substring(@c,1,charindex('|',@c)-1)
set @c=right(@c,len(@c)-charindex('|',@c)+1)
update @split set c4=substring(@c,1,charindex('|',@c)-1)
set @c=right(@c,len(@c)-charindex('|',@c)+1)
update @split set c5=substring(@c,1,charindex('|',@c)-1)
set @c=right(@c,len(@c)-charindex('|',@c)+1)
update @split set c6=substring(@c,1,charindex('|',@c)-1)
set @c=right(@c,len(@c)-charindex('|',@c)+1)
update @split set c7=substring(@c,1,charindex('|',@c)-1)
set @c=right(@c,len(@c)-charindex('|',@c)+1)
update @split set c8=substring(@c,1,charindex('|',@c)-1)
set @c=right(@c,len(@c)-charindex('|',@c)+1)
update @split set c9=substring(@c,1,charindex('|',@c)-1)
set @c=right(@c,len(@c)-charindex('|',@c)+1)
update @split set c10=substring(@c,1,charindex('|',@c)-1)
set @c=right(@c,len(@c)-charindex('|',@c)+1)
update @split set c11=substring(@c,1,charindex('|',@c)-1)
return
end
go
select 日期,(select c1 from dbo.splitstring(c)) as 登记号,
(select c2 from dbo.splitstring(c)) as 处方号,
(select c3 from dbo.splitstring(c)) as 单号,
(select c4 from dbo.splitstring(c)) as 流水号,
(select c5 from dbo.splitstring(c)) as 项目名称,
(select c6 from dbo.splitstring(c)) as 单价,
(select c7 from dbo.splitstring(c)) as 数量,
(select c8 from dbo.splitstring(c)) as 人员,
(select c9 from dbo.splitstring(c)) as 经办人,
(select c10 from dbo.splitstring(c)) as 单位,
(select c11 from dbo.splitstring(c)) as 规格
from tmp
go
drop table tmp
drop function dbo.splitstring
/*
登记号 处方号 单号 流水号 项目名称 单价 数量 人员 经办人 单位 规格
---------- ---------- ----------- --------- ---------- ------ ---- ---- ---- ---- -----
0000004741 1000400821 81100390861 100016179 0.9%氯化钠注射液 5.3000 1.0 陶先明 -1 瓶 500ml
0000004742 1000400822 81100390862 100016175 0.9%氯化钠注射液 5.3000 1.0 陶先明 -1 瓶 500ml
0000004743 1000400823 81100390863 100016175 0.9%氯化钠注射液 5.3000 1.0 陶先明 -1 瓶 500ml
0000004744 1000400824 81100390865 100016174 0.9%氯化钠注射液 5.3000 1.0 陶先明 -1 瓶 500ml
0000004745 1000400825 81100390866 100016173 0.9%氯化钠注射液 5.3000 1.0 陶先明 -1 瓶 500ml
0000004746 1000400826 81100390867 100016172 0.9%氯化钠注射液 5.3000 1.0 陶先明 -1 瓶 500ml
0000004747 1000400827 81100390868 100016171 0.9%氯化钠注射液 5.3000 1.0 陶先明 -1 瓶 500ml
*/
create database jinjazz_test
go
use jinjazz_test
go
--建立测试环境
set nocount on
create table test(日期 varchar(20),参数 varchar(200))
insert into test select '2008-10-1','0000004741|1000400821|81100390861|100016179|0.9%氯化钠注射液|5.3000|1.0|陶先明|-1|瓶|500ml|'
insert into test select '2008-10-1','0000004742|1000400822|81100390862|100016175|0.9%氯化钠注射液|5.3000|1.0|陶先明|-1|瓶|500ml|'
insert into test select '2008-10-1','0000004743|1000400823|81100390863|100016175|0.9%氯化钠注射液|5.3000|1.0|陶先明|-1|瓶|500ml|'
insert into test select '2008-10-1','0000004744|1000400824|81100390865|100016174|0.9%氯化钠注射液|5.3000|1.0|陶先明|-1|瓶|500ml|'
insert into test select '2008-10-1','0000004745|1000400825|81100390866|100016173|0.9%氯化钠注射液|5.3000|1.0|陶先明|-1|瓶|500ml|'
insert into test select '2008-10-1','0000004746|1000400826|81100390867|100016172|0.9%氯化钠注射液|5.3000|1.0|陶先明|-1|瓶|500ml|'
insert into test select '2008-10-1','0000004747|1000400827|81100390868|100016171|0.9%氯化钠注射液|5.3000|1.0|陶先明|-1|瓶|500ml|'
go
create table test1(登记号 varchar(20),处方号 varchar(20),单号 varchar(20),流水号 varchar(20),项目名称 varchar(20),单价 varchar(20),数量 varchar(20),人员 varchar(20),经办人 varchar(20),单位 varchar(20),规格 varchar(20))
go
--测试
EXEC master..xp_cmdshell 'bcp "select left(参数,len(参数)-1) from jinjazz_test.dbo.test" queryout e:\DT.txt -c -S10.1.3.252 -Usa -Psqltest'
EXEC master..xp_cmdshell 'bcp "jinjazz_test.dbo.test1" in e:\DT.txt -c -S10.1.3.252 -Usa -Psqltest -t"|"'
select * from test1
go
use master
go
drop database jinjazz_test
/*
登记号 处方号 单号 流水号 项目名称 单价 数量 人员 经办人 单位 规格
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
0000004741 1000400821 81100390861 100016179 0.9%氯化钠注射液 5.3000 1.0 陶先明 -1 瓶 500ml
0000004742 1000400822 81100390862 100016175 0.9%氯化钠注射液 5.3000 1.0 陶先明 -1 瓶 500ml
0000004743 1000400823 81100390863 100016175 0.9%氯化钠注射液 5.3000 1.0 陶先明 -1 瓶 500ml
0000004744 1000400824 81100390865 100016174 0.9%氯化钠注射液 5.3000 1.0 陶先明 -1 瓶 500ml
0000004745 1000400825 81100390866 100016173 0.9%氯化钠注射液 5.3000 1.0 陶先明 -1 瓶 500ml
0000004746 1000400826 81100390867 100016172 0.9%氯化钠注射液 5.3000 1.0 陶先明 -1 瓶 500ml
0000004747 1000400827 81100390868 100016171 0.9%氯化钠注射液 5.3000 1.0 陶先明 -1 瓶 500ml
*/
create table tmp(日期 datetime,c nvarchar(1000))
insert into tmp select '2008-10-1','0000004741|1000400821|81100390861|100016179|0.9%氯化钠注射液|5.3000|1.0|陶先明|-1|瓶|500ml|'
insert into tmp select '2008-10-1','0000004742|1000400822|81100390862|100016175|0.9%氯化钠注射液|5.3000|1.0|陶先明|-1|瓶|500ml|'
insert into tmp select '2008-10-1','0000004743|1000400823|81100390863|100016175|0.9%氯化钠注射液|5.3000|1.0|陶先明|-1|瓶|500ml|'
insert into tmp select '2008-10-1','0000004744|1000400824|81100390865|100016174|0.9%氯化钠注射液|5.3000|1.0|陶先明|-1|瓶|500ml|'
insert into tmp select '2008-10-1','0000004745|1000400825|81100390866|100016173|0.9%氯化钠注射液|5.3000|1.0|陶先明|-1|瓶|500ml|'
insert into tmp select '2008-10-1','0000004746|1000400826|81100390867|100016172|0.9%氯化钠注射液|5.3000|1.0|陶先明|-1|瓶|500ml|'
insert into tmp select '2008-10-1','0000004747|1000400827|81100390868|100016171|0.9%氯化钠注射液|5.3000|1.0|陶先明|-1|瓶|500ml|'
select substring(c,1,10) as 登记号,
substring(c,12,10) as 处方号,
substring(c,23,11) as 单号,
substring(c,35,9) as 流水号,
substring(c,45,10) as 项目名称,
substring(c,56,6) as 单价,
substring(c,63,3) as 数量,
substring(c,67,3) as 人员,
substring(c,71,2) as 经办人,
substring(c,74,1) as 单位,
substring(c,76,5) as 规格
from tmp
/*
登记号 处方号 单号 流水号 项目名称 单价 数量 人员 经办人 单位 规格
---------- ---------- ----------- --------- ---------- ------ ---- ---- ---- ---- -----
0000004741 1000400821 81100390861 100016179 0.9%氯化钠注射液 5.3000 1.0 陶先明 -1 瓶 500ml
0000004742 1000400822 81100390862 100016175 0.9%氯化钠注射液 5.3000 1.0 陶先明 -1 瓶 500ml
0000004743 1000400823 81100390863 100016175 0.9%氯化钠注射液 5.3000 1.0 陶先明 -1 瓶 500ml
0000004744 1000400824 81100390865 100016174 0.9%氯化钠注射液 5.3000 1.0 陶先明 -1 瓶 500ml
0000004745 1000400825 81100390866 100016173 0.9%氯化钠注射液 5.3000 1.0 陶先明 -1 瓶 500ml
0000004746 1000400826 81100390867 100016172 0.9%氯化钠注射液 5.3000 1.0 陶先明 -1 瓶 500ml
0000004747 1000400827 81100390868 100016171 0.9%氯化钠注射液 5.3000 1.0 陶先明 -1 瓶 500ml
*/
select 'select '''+replace(参数,'|',''',''') into #temptable from tmp
/*
功能:实现split功能的函数
*/
create function dbo.fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as
begin
declare @i int
set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)
while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))
set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end
if @inputstr <> '\'
insert @temp values(@inputstr)
return
end
go
--调用
declare @s varchar(1000)
set @s='1,2,3,4,5,6,7,8,55'
select * from dbo.fn_split(@s,',')
drop function dbo.fn_split