SqlServer 一段文本中获取11位的手机号码

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
*/

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!