create function dbo.fn_DelCharacter (@p varchar(8000)) returns varchar(8000) as begin declare @i int declare @ret varchar(8000) set @ret='' set @i=1 while @i<len(@p) begin if substring(@p,@i,1) in ('0','1','2','3','4','5','6','7','8','9') set @ret=@ret + substring(@p,@i,1) set @i=@i+1 end return @retend go --创建测试表create table T(f1 varchar(8000)) insert into T select 'xC' insert into T select '123P' insert into T select '34L' insert into T select 'M230' insert into T select 'H98G'select case when dbo.fn_DelCharacter (f1)='' then f1 else dbo.fn_DelCharacter (f1) end from Tgodrop function dbo.fn_DelCharacter drop table T
--创建用户自定义函数 create function dbo.fn_DelCharacter (@p varchar(8000)) returns varchar(8000) as begin declare @i int declare @ret varchar(8000) set @ret='' set @i=1 while @i<len(@p) begin if substring(@p,@i,1) in ('0','1','2','3','4','5','6','7','8','9') set @ret=@ret + substring(@p,@i,1) set @i=@i+1 end return @retend go --创建测试表create table T(f1 varchar(8000)) --追加测试数据 insert into T select 'xC' insert into T select '123P' insert into T select '34L' insert into T select 'M230' insert into T select 'H98G' --查询数据 select case when dbo.fn_DelCharacter (f1)='' then f1 else dbo.fn_DelCharacter (f1) end from Tgo --删除函数、测试表 drop function dbo.fn_DelCharacter drop table T
--再改一下--创建用户自定义函数 create function dbo.fn_DelCharacter (@p varchar(8000)) returns varchar(8000) as begin declare @i int declare @ret varchar(8000) set @ret='' set @i=1 while @i<len(@p) begin if substring(@p,@i,1) in ('0','1','2','3','4','5','6','7','8','9') set @ret=@ret + substring(@p,@i,1) set @i=@i+1 end return @retend go --创建测试表create table T(Xc varchar(8000)) --追加测试数据 insert into T select '123P' insert into T select '34L' insert into T select 'M230' insert into T select 'H98G' --查询数据 select dbo.fn_DelCharacter (Xc) from Tgo --删除函数、测试表 drop function dbo.fn_DelCharacter drop table T
create table tab(xc varchar(10)) insert tab select '123P' union all select '34L' union all select 'M230' union all select 'H98G' --要求得到其中的数字部分select patindex('%[0-9]%',xc),len(xc),len(xc)-patindex('%[0-9]%',xc), right(xc,len(xc)-patindex('%[0-9]%',xc)+1), patindex('%[A-Z]%',right(xc,len(xc)-patindex('%[0-9]%',xc)+1)), substring(xc,patindex('%[0-9]%',xc),(case when patindex('%[A-Z]%',right(xc,len(xc)-patindex('%[0-9]%',xc)+1))=0 then len(right(xc,len(xc)-patindex('%[0-9]%',xc)+1)) else patindex('%[A-Z]%',right(xc,len(xc)-patindex('%[0-9]%',xc)+1))-1 end)) from tab drop table tab
--創建測試環境 Create Table TEST(xC Varchar(100)) --插入數據 Insert Into TEST Select '123P' Union All Select '34L' Union All Select 'M230' Union All Select 'H98G' GO --創建函數 Create Function F_GetNumeric(@xC Varchar(100)) Returns Varchar(100) As Begin Declare @NewxC Varchar(100) Select @NewxC = '' While(Len(@xC) > 0) Begin If (ASCII(Left(@xC, 1)) Between 48 And 57) Select @NewxC = @NewxC + Left(@xC, 1) Select @xC = Stuff(@xC, 1, 1, '') End Return @NewxC End GO --測試 Select dbo.F_GetNumeric(xC) As xC From TEST GO --刪除測試環境 Drop Table TEST Drop Function F_GetNumeric --結果 /* xC 123 34 230 98 */
create table t ( x varchar(100) ) insert into t select 'd458d' union all select '3424' union all select 'wikdd2' union all select '323i'create function aa(@s varchar(100)) returns varchar(100) as begin declare @len INT declare @i int set @i=0 set @len=len(@s) while @i<=@len+1 begin IF ascii(upper(substring(@s,@i,1)))>=65 and ascii(upper(substring(@s,@i,1)))<=90 begin set @s=ltrim(replace(@s,substring(@s,@i,1),'')) end set @i=@i+1 end return @s endselect * from t select dbo.aa(x) from t---------------------------------------------------------------------------------------------------- 458 3424 i2 323(4 row(s) affected)
create function dbo.fn_DelCharacter
(@p varchar(8000))
returns varchar(8000)
as
begin
declare @i int
declare @ret varchar(8000) set @ret=''
set @i=1 while @i<len(@p)
begin
if substring(@p,@i,1) in ('0','1','2','3','4','5','6','7','8','9')
set @ret=@ret + substring(@p,@i,1)
set @i=@i+1
end return @retend
go
--创建测试表create table T(f1 varchar(8000))
insert into T select 'xC'
insert into T select '123P'
insert into T select '34L'
insert into T select 'M230'
insert into T select 'H98G'select case when dbo.fn_DelCharacter (f1)='' then f1 else dbo.fn_DelCharacter (f1) end
from Tgodrop function dbo.fn_DelCharacter
drop table T
create function dbo.fn_DelCharacter
(@p varchar(8000))
returns varchar(8000)
as
begin
declare @i int
declare @ret varchar(8000) set @ret=''
set @i=1 while @i<len(@p)
begin
if substring(@p,@i,1) in ('0','1','2','3','4','5','6','7','8','9')
set @ret=@ret + substring(@p,@i,1)
set @i=@i+1
end return @retend
go
--创建测试表create table T(f1 varchar(8000))
--追加测试数据
insert into T select 'xC'
insert into T select '123P'
insert into T select '34L'
insert into T select 'M230'
insert into T select 'H98G'
--查询数据
select case when dbo.fn_DelCharacter (f1)='' then f1 else dbo.fn_DelCharacter (f1) end
from Tgo
--删除函数、测试表
drop function dbo.fn_DelCharacter
drop table T
create function dbo.fn_DelCharacter
(@p varchar(8000))
returns varchar(8000)
as
begin
declare @i int
declare @ret varchar(8000) set @ret=''
set @i=1 while @i<len(@p)
begin
if substring(@p,@i,1) in ('0','1','2','3','4','5','6','7','8','9')
set @ret=@ret + substring(@p,@i,1)
set @i=@i+1
end return @retend
go
--创建测试表create table T(Xc varchar(8000))
--追加测试数据
insert into T select '123P'
insert into T select '34L'
insert into T select 'M230'
insert into T select 'H98G'
--查询数据
select dbo.fn_DelCharacter (Xc)
from Tgo
--删除函数、测试表
drop function dbo.fn_DelCharacter
drop table T
insert tab
select '123P'
union all
select '34L'
union all
select 'M230'
union all
select 'H98G'
--要求得到其中的数字部分select patindex('%[0-9]%',xc),len(xc),len(xc)-patindex('%[0-9]%',xc),
right(xc,len(xc)-patindex('%[0-9]%',xc)+1),
patindex('%[A-Z]%',right(xc,len(xc)-patindex('%[0-9]%',xc)+1)),
substring(xc,patindex('%[0-9]%',xc),(case when patindex('%[A-Z]%',right(xc,len(xc)-patindex('%[0-9]%',xc)+1))=0 then len(right(xc,len(xc)-patindex('%[0-9]%',xc)+1)) else patindex('%[A-Z]%',right(xc,len(xc)-patindex('%[0-9]%',xc)+1))-1 end))
from tab
drop table tab
(4 行受影响)
----------- ----------- ----------- ---------- ----------- ----------
1 4 3 123P 4 123
1 3 2 34L 3 34
2 4 2 230 0 230
2 4 2 98G 3 98(4 行受影响)
gaojier1000 大大的方法有局限性.如果有的数据 是 1A1 或者 1A1A1
这样有穿插的就不行了
Create Table TEST(xC Varchar(100))
--插入數據
Insert Into TEST Select '123P'
Union All Select '34L'
Union All Select 'M230'
Union All Select 'H98G'
GO
--創建函數
Create Function F_GetNumeric(@xC Varchar(100))
Returns Varchar(100)
As
Begin
Declare @NewxC Varchar(100)
Select @NewxC = ''
While(Len(@xC) > 0)
Begin
If (ASCII(Left(@xC, 1)) Between 48 And 57)
Select @NewxC = @NewxC + Left(@xC, 1)
Select @xC = Stuff(@xC, 1, 1, '')
End
Return @NewxC
End
GO
--測試
Select dbo.F_GetNumeric(xC) As xC From TEST
GO
--刪除測試環境
Drop Table TEST
Drop Function F_GetNumeric
--結果
/*
xC
123
34
230
98
*/
(
x varchar(100)
)
insert into t
select 'd458d' union all
select '3424' union all
select 'wikdd2' union all
select '323i'create function aa(@s varchar(100))
returns varchar(100)
as
begin
declare @len INT
declare @i int
set @i=0
set @len=len(@s)
while @i<=@len+1
begin
IF ascii(upper(substring(@s,@i,1)))>=65 and ascii(upper(substring(@s,@i,1)))<=90
begin
set @s=ltrim(replace(@s,substring(@s,@i,1),''))
end
set @i=@i+1
end
return @s
endselect * from t
select dbo.aa(x) from t----------------------------------------------------------------------------------------------------
458
3424
i2
323(4 row(s) affected)