实现 请将13212341221和手机号15121567886提取出来
运行结果:@str1=13212341221 @str2=15121567886
-- 创建临时表
IF OBJECT_ID('Test.dbo.#temptest','U') IS NOT NULL DROP TABLE dbo.#temptest;
GO
select id,MergeContractRemark,Tel,UserName,TeamName,OrgName,Method,Promotion,ExtendSubject ,customerid,tel1,tel2
INTO dbo.#temptest
from LK_Test
ORDER BY id;
SELECT * FROM dbo.#temptest;
DECLARE
@id AS INT;
WHILE EXISTS(SELECT id FROM dbo.#temptest)
BEGIN
-- 也可以使用top 1
--SET @id=1
SET ROWCOUNT 1
-- 声明变量
DECLARE @str VARCHAR(MAX)
DECLARE @str1 VARCHAR(MAX)
DECLARE @str2 VARCHAR(MAX)
DECLARE @validchars VARCHAR(MAX)
SELECT @id= id, @str= MergeContractRemark FROM dbo.#temptest;
SET @validchars = '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
DECLARE @idx INT
SET @idx = PATINDEX('%'+ @validchars +'%',@str)
IF @idx > 0 AND (@idx = LEN(@str)-8 OR PATINDEX(SUBSTRING(@str,@idx+9,1),'[0-9]')=0)
begin
SET @str1=SUBSTRING(@str ,PATINDEX('%'+ @validchars +'%',@str), 11);
if PATINDEX('%[^0-9|.|-|+]%',@str1)<>0
begin
SET @str1 = '';
end
set @str2=SUBSTRING(replace(@str,@str1,'') ,PATINDEX('%'+ @validchars +'%',replace(@str,@str1,'')), 11);
if PATINDEX('%[^0-9|.|-|+]%',@str2)<>0
begin
SET @str2 = '';
end
end
ELSE
begin
SET @str1 = '';
SET @str2 = '';
end
SELECT @str1,@str2
UPDATE LK_Test SET tel1= @str1,tel2=@str2 WHERE id=@id;
SET ROWCOUNT 1
DELETE FROM dbo.#temptest WHERE @id=@id;
--set @id+=1
END
--SELECT * from LK_Test where PATINDEX('%[^0-9|.|-|+]%',tel2) <>0 or(PATINDEX('%[^0-9|.|-|+]%',tel1) <>0)