set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[GET_NUMBER2](@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^0-9]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'')
END
RETURN @S
END
这个函数我执行，发现只能去掉其中一个字符串的中文，[email protected]，执行结果居然都是同一个结果
------解决方案--------------------
- SQL code
ALTER FUNCTION [dbo].[GET_NUMBER2](@S VARCHAR(100))RETURNS VARCHAR(100)ASBEGINWHILE PATINDEX('%[吖-座]%',@S) > 0BEGINset @s=stuff(@s,patindex('%[吖-座]%',@s),1,'')ENDRETURN @SEND
------解决方案--------------------
- SQL code
select *,dbo.GET_NUMBER2([列名]) from tb --列名不能加引号
------解决方案--------------------
你这个提取数字的吗？
------解决方案--------------------
- SQL code
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')BEGIN DROP TABLE tbaENDGOCREATE TABLE tba( col1 VARCHAR(100))GOINSERT INTO tbaSELECT '3445wersrede23sde' UNIONSELECT '937846rdjkdie37334'GOIF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'GET_NUMBER2')BEGIN DROP FUNCTION GET_NUMBER2ENDGOCREATE FUNCTION GET_NUMBER2(@S VARCHAR(100))RETURNS VARCHAR(100)ASBEGINWHILE PATINDEX('%[^0-9]%',@S) > 0BEGINset @s=stuff(@s,patindex('%[^0-9]%',@s),1,'')ENDRETURN @SENDGOSELECT dbo.GET_NUMBER2(col1) AS col1FROM tbacol134452393784637334