34,576
社区成员
发帖
与我相关
我的任务
分享
IF OBJECT_id('A') IS NOT NULL
DROP TABLE A
CREATE TABLE A(TNAME VARCHAR(200))
INSERT A(TNAME)
SELECT '00125&&050000&&小明'
UNION ALL
SELECT '00126&&060000&&小李'
----(一步一个脚印)
SELECT MAX(LC.NAME1) AS NAME1, MAX(LC.NAME2) AS NAME2,MAX(LC.NAME3) AS NAME3
FROM
(
SELECT LO.ID / 3 AS MARK ,
(CASE WHEN (LO.ID % 3 = 1) THEN LO.TNAME ELSE '' END) AS NAME2,
(CASE WHEN (LO.ID % 3 = 2) THEN LO.TNAME ELSE '' END) AS NAME3,
(CASE WHEN (LO.ID % 3 = 0) THEN LO.TNAME ELSE '' END) AS NAME1
FROM
(
Select
ROW_NUMBER() OVER(ORDER BY GETDATE()) -1 AS ID, B.TNAME
From
(select TNAME=convert(xml,'<root><v>'+replace(TNAME,'&&','</v><v>')+'</v></root>') from A) T
outer apply
(select TNAME=C.v.value('.','nvarchar(100)') from T.TNAME.nodes('/root/v')C(v) )B
) LO
) LC
GROUP BY LC.MARK
create index IX_Name on AA ([name])
select left([name],charindex('&',[name])-1) as name1, left((select substring([name],charindex('&',[name])+2,len([name])-(charindex('&',[name])+2)) from AA),(select
charindex('&',(select substring([name],charindex('&',[name])+2,len([name])-(charindex('&',[name])+2)))) from AA)-1) as name2,reverse(left(reverse([name]),charindex('&',reverse([name]))-1)) as name3 from AA
name1 name2 name3
---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
00125 050000 小明
(1 行受影响)
create table AA
(
[name] nvarchar(100)
)
insert into AA select '00125&&050000&&小明'
--1
select left([name],charindex('&',[name])-1) as name1 from AA
--2
select left((select substring([name],charindex('&',[name])+2,len([name])-7) from AA),(select
charindex('&',(select substring([name],charindex('&',[name])+2,len([name])-7))) from AA)-1) from AA
--3
select reverse(left(reverse([name]),charindex('&',reverse([name]))-1)) from AA
select left([name],charindex('&',[name])-1) as name1, left((select substring([name],charindex('&',[name])+2,len([name])-7) from AA),(select
charindex('&',(select substring([name],charindex('&',[name])+2,len([name])-7))) from AA)-1) as name2,reverse(left(reverse([name]),charindex('&',reverse([name]))-1)) as name3 from AA
if object_id('tb1') is not null
drop table tb1
go
create table tb1(name varchar(100))
go
insert into tb1 select '00125&&050000&&小明'
if object_id('tb2') is not null
drop table tb2
go
create table tb2(name1 varchar(100),name2 varchar(100),name3 varchar(100))
go
create proc insert_tb1_to_tb2
as
insert into tb2
select substring(replace(name,'&&',','),1,charindex(',',replace(name,'&&',','))-1) as name1,
substring(replace(name,'&&',','),charindex(',',replace(name,'&&',','))+1,charindex(',',replace(name,'&&',','),charindex(',',replace(name,'&&',',')))) as name2,
reverse(substring(replace(reverse(name),'&&',','),1,charindex(',',replace(reverse(name),'&&',','))-1)) as name3
from tb1
-- 调用存储过程
exec insert_tb1_to_tb2
select * from tb2
导入到另一表B:
表B已存在:
insert b
select
parsename(replace(name,'&&','.'),3) name1,
parsename(replace(name,'&&','.'),2) name2,
parsename(replace(name,'&&','.'),1) name3
from a
表B不存在:
select
into b
parsename(replace(name,'&&','.'),3) name1,
parsename(replace(name,'&&','.'),2) name2,
parsename(replace(name,'&&','.'),1) name3
from a
if object_id('[a]') is not null drop table [a]
go
create table [a]([name] varchar(30))
insert [a] select '00125&&050000&&小明'
select
parsename(replace(name,'&&','.'),3) name1,
parsename(replace(name,'&&','.'),2) name2,
parsename(replace(name,'&&','.'),1) name3
from a
/*
name1 name2 name3
------------------------------ ------------------------------ ------------------------------
00125 050000 小明
(1 行受影响)
*/
if object_id('[a]') is not null drop table [a]
go
create table [a]([name] varchar(30))
insert [a] select '00125&&050000&&小明'
select
left(name,charindex('&',name)-1) name1,
left(right(name,len(name)-charindex('&',name)-1),charindex('&',right(name,len(name)-charindex('&',name)-1))-1) name2,
reverse(left(reverse(name),charindex('&',reverse(name))-1)) name3
from a
/*
name1 name2 name3
------------------------------ ------------------------------ ------------------------------
00125 050000 小明
(1 行受影响)
*/