declare @t table(Tid varchar(20))
insert into @t select rtrim('1-- ')
insert into @t select rtrim('2-b- ')
insert into @t select rtrim('3-c-d ')
insert into @t select rtrim('4--e ')
insert into @t select rtrim('--fg ')
insert into @t select rtrim('5-x-yyy')select parseName(replace(Tid,'-','.'),1) as partIII from @t where parseName(replace(Tid,'-','.'),1) is not null/*
partIII
----------------------
d
e
fg
yyy
*/
declare @t table(Tid varchar(20))
insert into @t select rtrim('1-- ')
insert into @t select rtrim('2-b- ')
insert into @t select rtrim('3-c-d ')
insert into @t select rtrim('4--e ')
insert into @t select rtrim('--fg ')
insert into @t select rtrim('5-x-yyy')select
*
from
(select reverse(left(reverse(Tid),charindex('-',reverse(Tid))-1)) as partIII from @t) t
where
partIII!=''/*
partIII
----------------------
d
e
fg
yyy
*/
insert into @t select rtrim('1-- ')
insert into @t select rtrim('2-b- ')
insert into @t select rtrim('3-c-d ')
insert into @t select rtrim('4--e ')
insert into @t select rtrim('--fg ')
insert into @t select rtrim('5-x-yyy')
select * from @t where Tid like '%-%-_%'
/*
--------------------
3-c-d
4--e
--fg
5-x-yyy(4 行受影响)
*/
if object_id('tb','u')is not null drop table tb
go
create table tb(Tid varchar(10))
insert tb select '1--'
insert tb select '2-b-'
insert tb select '3-c-d'
insert tb select '4--e'
insert tb select '--fg'
insert tb select '5-x-yyy'
select right(tid,len(tid)-charindex('-',tid,charindex('-',tid))-1) from tb where tid like'%e%' or tid like'%d%' or tid like'%fg%' or tid like'%yyy%'
/*----------
-d
e
fg
-yyy
*/
if object_id('tb','u')is not null drop table tb
go
create table tb(Tid varchar(10))
insert tb select '1--'
insert tb select '2-b-'
insert tb select '3-c-d'
insert tb select '4--e'
insert tb select '--fg'
insert tb select '5-x-yyy'
select replace(right(tid,len(tid)-charindex('-',tid,charindex('-',tid))-1),'-','') from tb where tid like'%e%' or tid like'%d%' or tid like'%fg%' or tid like'%yyy%'
/*----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
d
e
fg
yyy
*/
declare @t table(Tid varchar(20))
insert into @t select rtrim('1-- ')
insert into @t select rtrim('2-b- ')
insert into @t select rtrim('3-c-d ')
insert into @t select rtrim('4--e ')
insert into @t select rtrim('--fg ')
insert into @t select rtrim('5-x-yyy')select
*
from
(select
stuff(partII,1,charindex('-',partII),'') as partIII
from
(select stuff(Tid,1,charindex('-',Tid),'') as partII from @t) t1) t2
where
partIII!=''/*
partIII
----------------------
d
e
fg
yyy
*/
感谢各位回复,正测试中
如果通过Ado.net的SQL语句来访问数据库而不是存储过程,又该怎么办呢?
没人用存储过程,select语句之外的语句,只用来生成示例数据。
--1.
select parseName(replace(Tid,'-','.'),1) as partIII from @t where parseName(replace(Tid,'-','.'),1) is not null
--2.
select
*
from
(select reverse(left(reverse(Tid),charindex('-',reverse(Tid))-1)) as partIII from @t) t
where
partIII!=''
--3.
select
*
from
(select
stuff(partII,1,charindex('-',partII),'') as partIII
from
(select stuff(Tid,1,charindex('-',Tid),'') as partII from @t) t1) t2
where
partIII!=''
那可以用下面的:declare @t table(Tid varchar(20))
insert into @t select rtrim('1-- ')
insert into @t select rtrim('2-b- ')
insert into @t select rtrim('3-c-d ')
insert into @t select rtrim('4--e ')
insert into @t select rtrim('--fg ')
insert into @t select rtrim('5-x-yyy')
select stuff(Tid, 1, charindex('-', Tid, charindex('-', Tid)+1),'')
from @t where Tid like '%-%-_%'
/*
-------------------
d*/
e
fg
yyy(4 行受影响)