if object_id('test') is not null
drop table test
go
create table test(notes varchar(100))
insert into test select 'STshanghai-Ton(0000933)'
insert into test select 'T&S - hccy(0000934)'
insert into test select 'Lorstem -dick(0000935)'
insert into test select 'Hutchin - Lanyd(0000937) 'select
substring(notes,charindex('(',notes)+1,charindex(')',notes)-charindex('(',notes)-1) as id,
left(notes,charindex('-',notes)-1) as Customer,
substring(notes,charindex('-',notes)+1,charindex('(',notes)-charindex('-',notes)-1) as Engineer
from testid Customer Engineer
0000933 STshanghai Ton
0000934 T&S hccy
0000935 Lorstem dick
0000937 Hutchin Lanyd
drop table test
go
create table test(notes varchar(100))
insert into test select 'STshanghai-Ton(0000933)'
insert into test select 'T&S - hccy(0000934)'
insert into test select 'Lorstem -dick(0000935)'
insert into test select 'Hutchin - Lanyd(0000937) 'select
substring(notes,charindex('(',notes)+1,charindex(')',notes)-charindex('(',notes)-1) as id,
left(notes,charindex('-',notes)-1) as Customer,
substring(notes,charindex('-',notes)+1,charindex('(',notes)-charindex('-',notes)-1) as Engineer
from testid Customer Engineer
0000933 STshanghai Ton
0000934 T&S hccy
0000935 Lorstem dick
0000937 Hutchin Lanyd
select
substring(notes,charindex('(',notes)+1,charindex(')',notes)-charindex('(',notes)-1) as id,
left(notes,charindex('-',notes)-1) as Customer,
substring(notes,charindex('-',notes)+1,charindex('(',notes)-charindex('-',notes)-1) as Engineer
from myTable
declare @myTable table (
notes varchar(200)
)
insert @myTable select
'STshanghai-Ton(0000933)'
union all select
'T&S - hccy(0000934)'
union all select
'Lorstem -dick(0000935)'
union all select
'Hutchin - Lanyd(0000937)'--拼凑语句
declare @sql varchar(8000)
select @sql=isnull(@sql+' union all ','')+' select '''+
replace (replace (replace (notes,'-',''' as Customer,''') ,'(',''' as Engineer,'''),')',''' as id')
from @myTable--显示结果
exec(@sql)--结果
Customer Engineer id
---------- -------- -------
STshanghai Ton 0000933
T&S hccy 0000934
Lorstem dick 0000935
Hutchin Lanyd 0000937(4 行受影响)
notes varchar(200)
)
insert @myTable select
'STshanghai-Ton(0000933)'
union all select
'T&S - hccy(0000934)'
union all select
'Lorstem -dick(0000935)'
union all select
'Hutchin - Lanyd(0000937)'declare @sql varchar(8000)
select @sql=isnull(@sql+' union all ','')+' select '''+
replace (replace (replace (notes,'-',''' as Customer,''') ,'(',''' as Engineer,'''),')',''' as id')
from @myTableexec('select id,Customer,Engineer from ('+@sql+') as t')--结果
id Customer Engineer
------- ---------- --------
0000933 STshanghai Ton
0000934 T&S hccy
0000935 Lorstem dick
0000937 Hutchin Lanyd(4 行受影响)
create table #mytable
(
notes varchar(30)
)insert into #mytable values('STshanghai-Ton(0000933)')
insert into #mytable values('T&S - hccy(0000934)')
insert into #mytable values('Lorstem -dick(0000935)')
insert into #mytable values('Hutchin - Lanyd(0000937)')
select substring(notes,charindex('(',notes)+1,charindex(')',notes)-charindex('(',notes)-1) as id,
left(notes,charindex('-',notes)-1) as Customer,
substring(notes,charindex('-',notes)+1,charindex('(',notes)-charindex('-',notes)-1) as Engineer
from #mytable------------------------
id Customer Engineer
------------------------------ ------------------------------ ------------------------------
0000933 STshanghai Ton
0000934 T&S hccy
0000935 Lorstem dick
0000937 Hutchin Lanyd(4 行受影响)
UNION ALL SELECT N'T&S - hccy(0000934)'
UNION ALL SELECT N'Lorstem -dick(0000935)'
UNION ALL SELECT N'Hutchin - Lanyd(0000937)'
/************/
/*Test Data*/
/***fcuandy**/
/*2009-01-04*/
/************/SELECT REPLACE(PARSENAME(n,1),')','') id,
RTRIM(PARSENAME(n,3)) Customer ,
RTRIM(LTRIM(PARSENAME(n,2)))Engineer
FROM
(
SELECT n = REPLACE(REPLACE(note,'-','.'),'(','.') FROM @t
) x
/*
0000933 STshanghai Ton
0000934 T&S hccy
0000935 Lorstem dick
0000937 Hutchin Lanyd
*/
接下来,我们看这个CHARINDEX命令:CHARINDEX('7.0', 'Microsoft SQL Server 2000')在这个例子中,CHARINDEX返回零,因为字符串“7.0” 不能在“Microsoft SQL Server”中被找到。接下来通过两个例子来看看如何使用CHARINDEX函数来解决实际的T-SQL问题。第一个例子,假设你要显示Northwind数据库Customer表前5行联系人列的Last Name。这是前5行数据
ContactName
------------------------------
Maria Anders
Ana Trujillo
Antonio Moreno
Thomas Hardy
Christina Berglund你可以看到,CustomName包含客户的First Name和Last Name,它们之间被一个空格隔开。我用CHARINDX函数确定两个名字中间空格的位置。通过这个方法,我们可以分析ContactName列的空格位置,这样我们可以只显示这个列的last name部分。这是显示Northwind的Customer表前5行last name的记录!
select top 5 substring(ContactName,charindex(' ',ContactName)+1 ,
len(ContactName)) as [Last Name] from Northwind.dbo.customers下面是这个命令输出的结果。
Last Name
------------------------------
Anders
Trujillo
Moreno
Hardy
BerglundCHARINDEX函数找到First Name和Last Name之间的空格,所以SUBSTRING函数可以分开ContactName列,这样就只有Last Name被选出。我在CHARINDEX函数返回的整数上加1,这样Last Name不是从空格开始。在第二个例子中,即如说你要计算记录中,某一个字段包含特定字符的所有记录数。CHARINDEX函数可以方便的解决你的问题。计算 Northwind.dbo.Customer表中Addresses字段中包含单词Road或者它的缩写Rd的记录数,选择语句类似这样:SELECT count(*) from Northwind.dbo.Customers
WHERE CHARINDEX('Rd',Address) > 0 or CHARINDEX('Road',Address)> 1
create table myTable
(
notes varchar(100)
)
goinsert myTable
select 'STshanghai-Ton(0000933)' union all
select 'T&S - hccy(0000934)' union all
select 'Lorstem -dick(0000935)' union all
select 'Hutchin - Lanyd(0000937)'
go
select Id号=replace(right(notes,len(notes)-CHARINDEX('(',notes)),')','')
,Customer = left(notes,CHARINDEX('-',notes)-1)
,Engineer = left(ltrim(right(notes,len(notes)-CHARINDEX('-',notes))),CHARINDEX('(',ltrim(right(notes,len(notes)-CHARINDEX('-',notes))))-1)
from myTable-----------------------------------------------result
Id号 Customer Engineer
0000933 STshanghai Ton
0000934 T&S hccy
0000935 Lorstem dick
0000937 Hutchin Lanyd
有的后面,有--Lee,之类的
(
notes nvarchar(50)
)
insert into #T
select 'STshanghai-Ton(0000933)' union all
select 'T&S - hccy(0000934)' union all
select 'Lorstem -dick(0000935)' union all
select 'Hutchin - Lanyd(0000937)'
select SUBSTRING(notes,CHARINDEX('(',notes,1)+1,CHARINDEX(')',notes,1)-CHARINDEX('(', notes,1)-1) 'Id',
SUBSTRING(notes,1,CHARINDEX('-',notes,1)-1) 'Customer',
SUBSTRING(notes,CHARINDEX('-',notes,1)+1,CHARINDEX('(', notes,1)-CHARINDEX('-',notes,1)-1) 'Engineer'
from #T
UNION ALL SELECT N'T&S - hccy(0000934) --asfasfdasfd'
UNION ALL SELECT N'Lorstem -dick(0000935)'
UNION ALL SELECT N'Hutchin - Lanyd(0000937) --asfasfdasfd asdfafdasdf'
/************/
/*Test Data*/
/***fcuandy**/
/*2009-01-04*/
/************/SELECT REPLACE(PARSENAME(n,1),')','') id,
RTRIM(PARSENAME(n,3)) Customer ,
RTRIM(LTRIM(PARSENAME(n,2)))Engineer
FROM
(
SELECT n = REPLACE(REPLACE(
STUFF(note + '--',CHARINDEX('--',note + '--'),10000,''),
'-','.'),'(','.') FROM @t
) x
create table myTable
(
notes varchar(100)
)
goinsert myTable
select 'STshanghai-Ton(0000933) --Lee ' union all
select 'T&S - hccy(0000934) --KK ' union all
select 'Lorstem -dick(0000935)' union all
select 'Hutchin - Lanyd(0000937)'
go
select Id号=replace(right(left(notes,CHARINDEX(')',notes)),len(left(notes,CHARINDEX(')',notes)))-CHARINDEX('(',left(notes,CHARINDEX(')',notes)))),')','')
,Customer = left(left(notes,CHARINDEX(')',notes)),CHARINDEX('-',left(notes,CHARINDEX(')',notes)))-1)
,Engineer = left(ltrim(right(left(notes,CHARINDEX(')',notes)),len(left(notes,CHARINDEX(')',notes)))-CHARINDEX('-',left(notes,CHARINDEX(')',notes))))),CHARINDEX('(',ltrim(right(left(notes,CHARINDEX(')',notes)),len(left(notes,CHARINDEX(')',notes)))-CHARINDEX('-',left(notes,CHARINDEX(')',notes))))))-1)
from myTable-----------------------------------------------result
Id号 Customer Engineer
0000933 STshanghai Ton
0000934 T&S hccy
0000935 Lorstem dick
0000937 Hutchin Lanyd
写的好象夸张了点,自己都看不懂了