SELECT LEFT(COL,PATINDEX('%[^0-9]%',COL)-1) FROM TB
create function f_str(@str varchar(100)) returns varchar(100) as begin while(patindex('%[^0-9]%',@str) > 0) set @str = stuff(@str,patindex('%[^0-9]%',@str),1,'') return @str end goselect dbo.f_str('服务123va3323ar423奋12斗234fv%352节')drop function f_str---------------------------------------------------------------------------------------------------- 123332342312234352(1 行受影响)
--Declare Function IF OBJECT_ID(N'dbo.RegexReplace') IS NOT NULL DROP FUNCTION dbo.RegexReplace GO CREATE FUNCTION dbo.RegexReplace ( @string VARCHAR(MAX), --被替换的字符串 @pattern VARCHAR(255), --替换模板 @replacestr VARCHAR(255), --替换后的字符串 @IgnoreCase INT = 0 --0区分大小写 1不区分大小写 ) RETURNS VARCHAR(8000) AS BEGIN DECLARE @objRegex INT, @retstr VARCHAR(8000) --创建对象 EXEC sp_OACreate 'VBScript.RegExp', @objRegex OUT --设置属性 EXEC sp_OASetProperty @objRegex, 'Pattern', @pattern EXEC sp_OASetProperty @objRegex, 'IgnoreCase', @IgnoreCase EXEC sp_OASetProperty @objRegex, 'Global', 1 --执行 EXEC sp_OAMethod @objRegex, 'Replace', @retstr OUT, @string, @replacestr --释放 EXECUTE sp_OADestroy @objRegex RETURN @retstr END GO
--Reconfigure EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'Ole Automation Procedures', 1 RECONFIGURE--Test drop table # CREATE TABLE # ( memo nvarchar(100) ) INSERT # select '0101我' union all select '02你' union all select '0301他' union all select '041还有谁'--Test SELECT num = dbo.RegexReplace(memo, '[^0-9]', '', 1) FROM # /* num 0101 02 0301 041 */
create function f_str(@str varchar(100))
returns varchar(100)
as
begin
while(patindex('%[^0-9]%',@str) > 0)
set @str = stuff(@str,patindex('%[^0-9]%',@str),1,'')
return @str
end
goselect dbo.f_str('服务123va3323ar423奋12斗234fv%352节')drop function f_str----------------------------------------------------------------------------------------------------
123332342312234352(1 行受影响)
IF OBJECT_ID(N'dbo.RegexReplace') IS NOT NULL
DROP FUNCTION dbo.RegexReplace
GO
CREATE FUNCTION dbo.RegexReplace
(
@string VARCHAR(MAX), --被替换的字符串
@pattern VARCHAR(255), --替换模板
@replacestr VARCHAR(255), --替换后的字符串
@IgnoreCase INT = 0 --0区分大小写 1不区分大小写
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @objRegex INT, @retstr VARCHAR(8000)
--创建对象
EXEC sp_OACreate 'VBScript.RegExp', @objRegex OUT
--设置属性
EXEC sp_OASetProperty @objRegex, 'Pattern', @pattern
EXEC sp_OASetProperty @objRegex, 'IgnoreCase', @IgnoreCase
EXEC sp_OASetProperty @objRegex, 'Global', 1
--执行
EXEC sp_OAMethod @objRegex, 'Replace', @retstr OUT, @string, @replacestr
--释放
EXECUTE sp_OADestroy @objRegex
RETURN @retstr
END
GO
--Reconfigure
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'Ole Automation Procedures', 1
RECONFIGURE--Test
drop table #
CREATE TABLE #
(
memo nvarchar(100)
)
INSERT #
select '0101我' union all
select '02你' union all
select '0301他' union all
select '041还有谁'--Test
SELECT num = dbo.RegexReplace(memo, '[^0-9]', '', 1) FROM #
/*
num
0101
02
0301
041
*/
AcHerat的不错
一二楼的不知是我输入错误还是什么问题 ,不行哦