if object_id('[tb]') is not null drop table [tb] go create table [tb]([col] varchar(11)) insert [tb] select '天气好1' union all select '我爱你12' union all select '美丽新世界6' select right(col,len(col)-patindex('%[0-9]%',col)+1) as col from tb/** col ----------- 1 12 6(所影响的行数为 3 行) **/ excel的话用 Select * From OpenRowSet('MICROSOFT.JET.OLEDB.4.0','EXCEL 8.0;HDR=YES;IMEX=2;DataBase=d:\test.xls',[sheet1$])
go
create table [tb]([col] varchar(11))
insert [tb]
select '天气好1' union all
select '我爱你12' union all
select '美丽新世界6'
select right(col,len(col)-patindex('%[0-9]%',col)+1) as col from tb/**
col
-----------
1
12
6(所影响的行数为 3 行)
**/
excel的话用
Select *
From
OpenRowSet('MICROSOFT.JET.OLEDB.4.0','EXCEL 8.0;HDR=YES;IMEX=2;DataBase=d:\test.xls',[sheet1$])