在列中拆分字符串并在列中添加值 [英] Split string in column and add value in column
问题描述
我有一个包含多行数据的表格，如下所示:
I have a table with several rows of data like this :
16 W:\2-Work\ALBO\00_Proposal\ALxO_Amendement #1_20091022_signed.pdf
17 W:\2-Work\ALBO\00_Proposal\Level1\ALBO_Amendment #1_20110418.docx
18 W:\2-Work\ALBO\00_Proposal\A\BR\T\X_#1_20110418_final.docx
19 W:\2-Work\ALBO\MyOptionl\AO_Amendment_2 August 2013.docx
我创建了从 Col1 到 Col10
我想用分隔符 '\'
这个想法是在每一列上都有:
The idea is to have on each column :
Col1 | Col2 | Col3 | Col4 | Col5 |etc...
W: 2-Work ALBO 00_Proposal ALxO_Amendement #1_20091022_signed.pdf
我知道如何使用 charindex 和 substring 但每行(8500 行)的 '\' 数量不同.
I know how to use charindex and substring but the number of '\' are different on each line (8500 rows).
你能帮我吗?
我使用的是 Microsoft SQL Server 2012.
I'm using Microsoft SQL Server 2012.
非常感谢
编辑 2014/06/24
Edit 2014/06/24
我的目标是生成完整路径和分割路径的 XML.
My goal is to generate an XML of the full path and split path.
实际上，这是我的想法:
Actually, here is my idea :
1 - 识别临时表中的所有 ID 以进行循环
1 - Identify all the ID in a temporary table to do loop
--On déclare une table tempo声明@IdTable 表(身份证号码，src nvarchar(max))
--On déclare une table tempo declare @IdTable Table ( id int, src nvarchar(max))
--在injecte tous les id existing de la table插入@IdTable (id, src)从albo中选择id，src
--On injecte tous les id existant de la table insert into @IdTable (id, src) select id, src from albo
--on déclare l'id de début en commencant par le plus petit声明@id int = (select min(id) from ALBO)
--on déclare l'id de début en commencant par le plus petit declare @id int = (select min(id) from ALBO)
--Tnat qu'il reste des ID on continue la boucle而@id 不为空开始
--Tnat qu'il reste des ID on continue la boucle while @id is not null begin
打印@id从 @IdTable 中选择 @id = min(id) 其中 ID > @id结尾--Fin de la boule des ID
print @id select @id = min(id) from @IdTable where ID > @id end --Fin de la boucle des ID
2 - 拆分每一行并更新列(Colx => Clolums 之前已创建)这段代码应该放在我之前的循环中.
2 - Split each row and update column (Colx => The Clolumns have been created before) This code should be placed into my previous loop.
声明@products varchar(max) = 'W:\2-Work\ALBO\13_WP Reporting\13_07_Monthly reports\13_07_01 Archives\2012\201211\Draft\ALBO-MR-201211\gp_scripts\v1\Top10_dure'声明@individual varchar(max) = null
Declare @products varchar(max) = 'W:\2-Work\ALBO\13_WP Reporting\13_07_Monthly reports\13_07_01 Archives\2012\201211\Draft\ALBO-MR-201211\gp_scripts\v1\Top10_duree_final.txt' Declare @individual varchar(max) = null
虽然 LEN(@products) > 0开始如果 PATINDEX('%\%',@products) > 0开始SET @individual = SUBSTRING(@products, 0, PATINDEX('%\%',@products))选择@individual --我必须用ID制作和更新
WHILE LEN(@products) > 0 BEGIN IF PATINDEX('%\%',@products) > 0 BEGIN SET @individual = SUBSTRING(@products, 0, PATINDEX('%\%',@products)) select @individual --i have to make and update with the ID
SET @products = SUBSTRING(@products, LEN(@individual + '\') + 1,
LEN(@products))
END
ELSE
BEGIN
SET @individual = @products
SET @products = NULL
print @individual
END
结束
推荐答案
正如其他人所说，这可能不是最好的做事方式，如果你解释你将用结果做什么，它可能会对我们有所帮助提供更好的选择
As others have said, this probably isn't the best way to do things, if you explain what you'll be doing with the results it might help us provide a better option
[另外，由于某些原因，下面代码的颜色显示很奇怪，所以将其复制并粘贴到您的 Sql 服务器中以更好地查看它]
[Also, for some reason the colours of the code below are showing up odd, so copy and paste it into your Sql server to see it better]
drop table #Path
create table #Path (item bigint,location varchar(1000))
insert into #Path
select 16 ,'W:\2-Work\ALBO\00_Proposal\ALxO_Amendement #1_20091022_signed.pdf' union
select 17 ,'W:\2-Work\ALBO\00_Proposal\Level1\ALBO_Amendment #1_20110418.docx' union
select 18 ,'W:\2-Work\ALBO\00_Proposal\A\BR\T\X_#1_20110418_final.docx' union
select 19 ,'W:\2-Work\ALBO\MyOptionl\AO_Amendment_2 August 2013.docx'
select * from #Path;
with Path_Expanded(item,subitem,location, start, ending, split)
as(
select item
, 1 --subitem begins at 1
, location -- full location path
, 0 --start searching the file from the 0 position
, charindex('\',location) -- find the 1st '\' charactor
, substring(location,0,charindex('\',location)) --return the string from the start position, 0, to the 1st '\' charactor
from #Path
union all
select item
, subitem+1 --add 1 to subitem
, location -- full location path
, ending+1 -- start searching the file from the position after the last '\' charactor
, charindex('\',location,ending+1)-- find the 1st '\' charactor that occurs after the last '\' charactor found
, case when charindex('\',location,ending+1) = 0 then substring(location,ending+1,1000) --if you cant find anymore '\', return everything else after the last '\'
else substring(location,ending+1, case when charindex('\',location,ending+1)-(ending+1) <= 0 then 0
else charindex('\',location,ending+1)-(ending+1) end )--returns the string between the last '\' charactor and the next '\' charactor
end
from Path_Expanded
where ending > 0 --stop once you can't find anymore '\' charactors
)
--pivots the results
select item
, max(case when subitem = 1 then split else '' end) as col1
, max(case when subitem = 2 then split else '' end) as col2
, max(case when subitem = 3 then split else '' end) as col3
, max(case when subitem = 4 then split else '' end) as col4
, max(case when subitem = 5 then split else '' end) as col5
, max(case when subitem = 6 then split else '' end) as col6
, max(case when subitem = 7 then split else '' end) as col7
, max(case when subitem = 8 then split else '' end) as col8
, max(case when subitem = 9 then split else '' end) as col9
, max(case when subitem = 10 then split else '' end) as col10
from Path_Expanded
group by item
您可能更喜欢将每个文件夹放在自己的行上，如果是这样，请用下面的查询替换上面的数据透视部分
you might prefer to have each folder on its own row, if so replace the pivot part above with the below query instead
select item
, subitem
, location
, split from Path_Expanded where item = 16
这篇关于在列中拆分字符串并在列中添加值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
