22,302
社区成员




USE [AdventureWorks2012];
GO
DECLARE @t_TB TABLE
(
COL VARCHAR(20)
)
;
INSERT INTO @t_TB VALUES
('ABC123')
,('ENA329')
,('EEE22W33')
,('F1W2C3E8')
,('DDDDDDD')
;
SELECT col,LEFT(subsrt, PATINDEX('%[^0-9]%', subsrt + 't') - 1) as new_num
FROM (
SELECT col,subsrt = SUBSTRING(col, pos, LEN(col))
FROM (
SELECT col, pos = PATINDEX('%[0-9]%', col)
FROM @t_TB
) d
) t
/*
col new_num
-------------------- --------------------
ABC123 123
ENA329 329
EEE22W33 22
F1W2C3E8 1
DDDDDDD
*/
-- 使用自定义的函数
create table test(id int , c1 varchar(20) , c2 varchar(20))
go
insert into test(id,c1) values(1,'河石家庄'),(2,'石家庄123'),(3,'石家庄1234X')
go
create function fn_getnumber(@str varchar(100))
returns varchar(100)
begin
declare @return varchar(100) = ''
declare @temp varchar(2)
while LEN(@str) > 0
begin
set @temp = SUBSTRING(@str,1,1)
if @temp between '0' and '9'
select @return = @return + @temp
set @str = SUBSTRING(@str,2,len(@str))
end
return @return
end
go
select * from test
go
update test set c2 = dbo.fn_getnumber(c1)
go
select * from test
go
drop table test
go
drop function fn_getnumber
go
(3 行受影响)
id c1 c2
----------- -------------------- --------------------
1 河石家庄 NULL
2 石家庄123 NULL
3 石家庄1234X NULL
(3 行受影响)
(3 行受影响)
id c1 c2
----------- -------------------- --------------------
1 河石家庄
2 石家庄123 123
3 石家庄1234X 1234
(3 行受影响)