declare @var varchar(4000)
set @var = 'a2e384c8-4329-40e1-8130-22482baae8de.doc|事中报告.doc'select
left(@var,charindex('|',@var)-1),
stuff(@var,1,charindex('|',@var),'')
set @var = 'a2e384c8-4329-40e1-8130-22482baae8de.doc|事中报告.doc'select
left(@var,charindex('|',@var)-1),
stuff(@var,1,charindex('|',@var),'')
declare @i int
set @str='a2e384c8-4329-40e1-8130-22482baae8de.doc|事中报告.doc'
set @i=CHARINDEX ('|',@str)select left(@str,@i -1 )
select substring(@str,@i + 1,888)
----结果
a2e384c8-4329-40e1-8130-22482baae8de.doc(所影响的行数为 1 行)
事中报告.doc(所影响的行数为 1 行)
declare @text varchar(100)
set @text='a2e384c8-4329-40e1-8130-22482baae8de.doc|事中报告.doc'
select substring(@text,0,charindex('|',@text)) as a,substring(@text,charindex('|',@text)+1,len(@text)) as b--输出结果----
-------------------------------------------------------------------
a b
a2e384c8-4329-40e1-8130-22482baae8de.doc 一个是事中报告.doc
set @var = 'a2e384c8-4329-40e1-8130-22482baae8de.doc|事中报告.doc'select
col1 = left(@var,charindex('|',@var)-1),
col2 = stuff(@var,1,charindex('|',@var),'')
--测试结果
col1 col2
---------------------------------------- ------------
a2e384c8-4329-40e1-8130-22482baae8de.doc 事中报告.doc
哇,大家抢的好凶呀~~~
declare @s as varchar(80)
set @s='a2e384c8-4329-40e1-8130-22482baae8de.doc|事中报告.doc'
select left(@s,(select len(@s))-(select charindex('|',reverse(@s)) ) ),right(@s,(select charindex('|',reverse(@s)))-1 )--结果
--------------------- ---------
a2e384c8-4329-40e1-8130-22482baae8de.doc 事中报告.doc(所影响的行数为 1 行)
insert @ta select 1, 'a2e384c8-4329-40e1-8130-22482baae8de.doc|事中报告.doc'
select
substring(value,1, patindex('%|%',value)-1),
substring(value,patindex('%|%',value)+1, datalength(value))
from @ta
insert into #T select 1, 'a2e384c8-4329-40e1-8130-22482baae8de.doc|事中報告.doc'
select
substring(value,1, charindex('|',value)-1) as Col1,
substring(value,charindex('|',value)+1, datalength(value)) as Col2
from #T
set @vString='a2e384c8-4329-40e1-8130-22482baae8de.doc|事中報告.doc'select left(@vString,charindex('|',@vString)-1),
right(@vString,len(@vString)-charindex('|',@vString))要是有多個可以用循環來操作。
declare @vString varchar(200)
declare @iCount int,@iPos int
set @vString='a|b|cc|dd|cc|ee|ff'
select @iCount=len(@vString)-len(replace(@vString,'|','')),@iPos=1
while(@iPos<=@iCount)
begin
select left(@vString,charindex('|',@vString)-1
select @iPos = @iPos+1,@vString=right(@vString,len(@vString)-charindex('|',@vString))
end
select @vString
/*
影響結果
a
b
cc
dd
cc
ee
ff
*/