CREATE TABLE #TEST(MENO VARCHAR(50))
INSERT INTO #TEST
SELECT '的奋斗'
UNION ALL
SELECT '12368597854的奋斗'
UNION ALL
SELECT '的发生15286952365规范'
UNION ALL
SELECT 'hh452hh还好12568956854改'
UNION ALL
SELECT '烦得很25663355的ggg336'
UNION ALL
SELECT '的风格大方地方地方125'
-- 只能逐个字符判断,编写函数
-- DROP FUNCTION DBO.GetMoblie
CREATE FUNCTION DBO.GetMoblie(@str varchar(2000))
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @char VARCHAR(2)
DECLARE @mark INT
DECLARE @return VARCHAR(20)
SET @mark = 0
SET @return = ''
WHILE (LEN(@str)>0)
BEGIN
SET @char=LEFT(@str,1)
IF(ISNUMERIC(@char)=1)
BEGIN
SET @mark = @mark +1
SET @return = @return + @char
END
ELSE
BEGIN
IF @mark = 11
RETURN @return
SET @mark = 0
SET @return = ''
SET @str=RIGHT(@str,LEN(@str)-1)
CONTINUE
END
SET @str=RIGHT(@str,LEN(@str)-1)
END
RETURN @return
END
GO
select DBO.GetMoblie(MENO)
from dbo.#TEST where MENO like '%[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'
/*结果:
12368597854
15286952365
12568956854
*/