各位大虾请帮帮小弟:如何去掉字符串中的数字,比较急呀,请各位帮帮我呀,谢谢啦。
CREATE TABLE TEST (Contact CHAR(2),Address CHAR(30))
INSERT TEST SELECT 'A','3995 Thomas Drive'
UNION ALL SELECT 'B','95 Dewberry Ct'
UNION ALL SELECT 'C','635 mill st'
UNION ALL SELECT 'D','3050 E 6TH ST'
UNION ALL SELECT 'E','3216 Lawndale Ave'
UNION ALL SELECT 'F','207 240th st.'
UNION ALL SELECT 'G','19 Lockhouse Rd. #14-3'现在想得到的结果是:去掉Address当中的数字。
如:
Contact Address
A Thomas Drive
B Dewberry Ct
C mill st
D E 6TH ST
E Lawndale Ave
F th st.
G Lockhouse Rd.
. .
. .
. .
. .
. .
. .
. .
. .
.
CREATE TABLE TEST (Contact CHAR(2),Address CHAR(30))
INSERT TEST SELECT 'A','3995 Thomas Drive'
UNION ALL SELECT 'B','95 Dewberry Ct'
UNION ALL SELECT 'C','635 mill st'
UNION ALL SELECT 'D','3050 E 6TH ST'
UNION ALL SELECT 'E','3216 Lawndale Ave'
UNION ALL SELECT 'F','207 240th st.'
UNION ALL SELECT 'G','19 Lockhouse Rd. #14-3'现在想得到的结果是:去掉Address当中的数字。
如:
Contact Address
A Thomas Drive
B Dewberry Ct
C mill st
D E 6TH ST
E Lawndale Ave
F th st.
G Lockhouse Rd.
. .
. .
. .
. .
. .
. .
. .
. .
.
replace(replace(replace(Address
replace(replace(replace(Address
replace(replace(replace(Address
,'1',''),'2',''),'3','')
,'4',''),'5',''),'6','')
,'7',''),'8',''),'9','')
,'0','')
如:
Contact Address
A Thomas Drive
B Dewberry Ct
C mill st
D E 6TH ST
E Lawndale Ave
F th st.
G Lockhouse Rd. **6不是数字吗?
INSERT TEST SELECT 'A','3995 Thomas Drive'
UNION ALL SELECT 'B','95 Dewberry Ct'
UNION ALL SELECT 'C','635 mill st'
UNION ALL SELECT 'D','3050 E 6TH ST'
UNION ALL SELECT 'E','3216 Lawndale Ave'
UNION ALL SELECT 'F','207 240th st.'
UNION ALL SELECT 'G','19 Lockhouse Rd. #14-3'
go
create function dbo.f_trimNumeric(@address char(30))
returns varchar(30)
as
begin
declare @re varchar(30)
declare @i int
select @re='',@i=1
while @i<=len(@address)
begin
if isnumeric(substring(@address,@i,1))=0
set @re=@re+substring(@address,@i,1)
set @i=@i+1
end
return ltrim(@re)
end
go
select Contact,dbo.f_trimNumeric(address) address from testdrop function f_trimNumeric
drop table test/*
Contact address
------- ------------------------------
A Thomas Drive
B Dewberry Ct
C mill st
D E TH ST
E Lawndale Ave
F th st
G Lockhouse Rd #(所影响的行数为 7 行)
*/
--1parsename
declare @col001 varchar(20)
set @col001 = '123 Kelly Harrigan'
select parsename(replace(@col001,' ','.'),3),
parsename(replace(@col001,' ','.'),2)+' '+parsename(replace(@col001,' ','.'),1)--2left,right
declare @col001 varchar(20)
set @col001 = '123 Kelly Harrigan'
select left(@col001,charindex(' ',@col001)) ,right(@col001,len(@col001)-charindex(' ',@col001))--3substring
declare @col001 varchar(20)
set @col001 = '123 Kelly Harrigan'
select substring(@col001,1,charindex(' ',@col001)-1)
,substring(@col001,charindex(' ',@col001),len(@col001)) --4stuff
declare @col001 varchar(20)
set @col001 = '123 Kelly Harrigan'
select stuff(@col001,charindex(' ',@col001),len(@col001),''),
stuff(@col001,1,charindex(' ',@col001),'')
replace(replace(replace(-- delete Address can normal run
replace(replace(replace(--
replace(replace(replace(Address
,'1',''),'2',''),'3','')
,'4',''),'5',''),'6','')
,'7',''),'8',''),'9','')
,'0','')
drop table test
CREATE TABLE TEST (Contact CHAR(2),Address CHAR(30))
INSERT TEST SELECT 'A','3995 Thomas Drive'
UNION ALL SELECT 'B','95 Dewberry Ct'
UNION ALL SELECT 'C','635 mill st'
UNION ALL SELECT 'D','3050 E 6TH ST'
UNION ALL SELECT 'E','3216 Lawndale Ave'
UNION ALL SELECT 'F','207 240th st.'
UNION ALL SELECT 'G','19 Lockhouse Rd. #14-3'
go
alter function dbo.f_trimNumeric(@address char(30))
returns varchar(30)
as
begin
declare @i int
declare @stop int
select @i=1,@stop=1
while (@i<=len(@address) and @stop=1)
begin
if (isnumeric(substring(@address,@i,1))=0 and substring(@address,@i,1)!='')
set @stop=2
set @i=@i+1
end
return ltrim(substring(@address,@i-1,len(@address)))
end
go
select Contact,dbo.f_trimNumeric(address) address from testdrop function f_trimNumeric
drop table test