错了 应该是 这个 select substring(field,1,5) as field from table
a|b|c|d|e|f|gselect substring(field,1,1) , substring(field,3,1) , substring(field,5,1) from table
select left(field,5) from tablename 这样应该可以~ 楼主试一下,有什么问题再贴出来~
left(field,5)哈哈!create runction ff( @s varchar(8000)=field, @split varchar(100) )returns @re table(col varchar(100)) as begin declare @splitlen int set @splitlen=len(@split+'a')-2 while charindex(@split,@s)>0 begin insert @re values(left(@s,charindex(@split,@s)-1)) set @s=stuff(@s,1,1charindex(@split,@s)+@splitlen,'') end insert @re values(@s) return end
declare @t table(field varchar(100)) insert into @t select 'a|bb|c|d|e|f|g' union select 'z|yz|x|w|v|u|n' declare @i int select left(field,charindex('|',field,charindex('|',field,charindex('|',field)+1)+1)-1) from @t
修正,考虑不足三个的,如 'z|y' 和'm' declare @t table(field varchar(100)) insert into @t select 'a|b|c|d|e|f|g' union select 'z|y|x|w|v|u|n' union select 'z|y' union select 'm'select case when len(field) - len(replace(field, '|', '')) >=3 then left(field,charindex('|',field,charindex('|',field,charindex('|',field)+1)+1)-1) else field end from @t
楼主试一下,有什么问题再贴出来~
@s varchar(8000)=field,
@split varchar(100)
)returns @re table(col varchar(100))
as
begin
declare @splitlen int
set @splitlen=len(@split+'a')-2
while charindex(@split,@s)>0
begin
insert @re values(left(@s,charindex(@split,@s)-1))
set @s=stuff(@s,1,1charindex(@split,@s)+@splitlen,'')
end
insert @re values(@s)
return
end
insert into @t select 'a|bb|c|d|e|f|g' union select 'z|yz|x|w|v|u|n'
declare @i int
select left(field,charindex('|',field,charindex('|',field,charindex('|',field)+1)+1)-1) from @t
declare @t table(field varchar(100))
insert into @t select 'a|b|c|d|e|f|g' union select 'z|y|x|w|v|u|n' union select 'z|y' union select 'm'select case when len(field) - len(replace(field, '|', '')) >=3
then left(field,charindex('|',field,charindex('|',field,charindex('|',field)+1)+1)-1) else field end from @t
我只是举个例子,abcdefg的内容是未知的,我只是知道这样的形式
select substring(field,1,1) , substring(field,3,1) , substring(field,5,1) from table????