如果是简单的号码,而且格式都是如 0000-000000-0000之类的格式,如前面几楼的, 替换掉'-'这个字符即可, 反之,提取数字即可, http://topic.csdn.net/u/20080713/00/77925c47-b7fa-4c1b-b307-0328e74a1c09.html /* 功能:提取数字 功能:提取英文 功能:提取中文 功能:过滤重复字符 功能:过滤重复字符2 作者:wzy_love_sly */ --提取数字 IF OBJECT_ID('DBO.GET_NUMBER2') IS NOT NULL DROP FUNCTION DBO.GET_NUMBER2 GO CREATE 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 GO --测试 PRINT DBO.GET_NUMBER('呵呵ABC123ABC') GO --123
把-替换掉即可。 replace(字段,'-','')
replace(字段,"-","") 替換字段里面有-的為空
DECLARE @TB TABLE(TEL VARCHAR(30)) INSERT INTO @TB SELECT '0592-5600623-213' UNION ALL SELECT '0592-3120623-213' UNION ALL SELECT '0594-3120623-8080808' UNION ALL SELECT '0595-1202623-213' UNION ALL SELECT '0596-5120623-6520623' CREATE FUNCTION F_GETTEL(@TEL VARCHAR(30),@I INT) RETURNS VARCHAR(20) AS BEGIN DECLARE @TMP VARCHAR(30),@A VARCHAR(10) SET @TEL=STUFF(@TEL,CHARINDEX('-',@TEL),1,'') IF @I=1 SET @TMP= LEFT(@TEL,CHARINDEX('-',@TEL)-1) ELSE BEGIN SET @A=RIGHT(@TEL,LEN(@TEL)-CHARINDEX('-',@TEL)) SET @TMP= LEFT(@TEL,CHARINDEX('-',@TEL)-1-LEN(@A))+@A END RETURN @TMP END SELECT * FROM @TB WHERE DBO.F_GETTEL(TEL,1)='05951202213' OR DBO.F_GETTEL(TEL,2)='05951202213'/* TEL ------------------------------ 0595-1202623-213 */
--010-110 --021-112-121 CREATE TABLE t1(TEL NVARCHAR(20)) INSERT INTO t1(TEL) SELECT '010-110' UNION ALL SELECT '021-112-121'GO DECLARE @s NVARCHAR(20) SET @s='010110' SELECT * FROM t1 WHERE CONVERT(NVARCHAR(20),Replace(TEL,'-',''))=@s GO DROP TABLE t1 GO
CREATE FUNCTION DBO.TEL_NUMBER(@S VARCHAR(20)) RETURNS VARCHAR(21) AS BEGIN DECLARE @T VARCHAR(2) WHILE PATINDEX('%[^0-9]%',@S) > 0 BEGIN IF PATINDEX('+%',@S) = 1 BEGIN SET @T='+' SET @S=STUFF(@S,PATINDEX('%[^0-9]%',@S),1,'') END ELSE SET @S=STUFF(@S,PATINDEX('%[^0-9]%',@S),1,'') END SET @S= @T + @S RETURN @S END DROP FUNCTION DBO.TEL_NUMBER
替换掉'-'这个字符即可,
反之,提取数字即可,
http://topic.csdn.net/u/20080713/00/77925c47-b7fa-4c1b-b307-0328e74a1c09.html
/*
功能:提取数字
功能:提取英文
功能:提取中文
功能:过滤重复字符
功能:过滤重复字符2 作者:wzy_love_sly
*/ --提取数字
IF OBJECT_ID('DBO.GET_NUMBER2') IS NOT NULL
DROP FUNCTION DBO.GET_NUMBER2
GO
CREATE 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
GO
--测试
PRINT DBO.GET_NUMBER('呵呵ABC123ABC')
GO
--123
replace(字段,'-','')
替換字段里面有-的為空
INSERT INTO @TB SELECT '0592-5600623-213'
UNION ALL SELECT '0592-3120623-213'
UNION ALL SELECT '0594-3120623-8080808'
UNION ALL SELECT '0595-1202623-213'
UNION ALL SELECT '0596-5120623-6520623'
CREATE FUNCTION F_GETTEL(@TEL VARCHAR(30),@I INT)
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @TMP VARCHAR(30),@A VARCHAR(10)
SET @TEL=STUFF(@TEL,CHARINDEX('-',@TEL),1,'')
IF @I=1
SET @TMP= LEFT(@TEL,CHARINDEX('-',@TEL)-1)
ELSE
BEGIN
SET @A=RIGHT(@TEL,LEN(@TEL)-CHARINDEX('-',@TEL))
SET @TMP= LEFT(@TEL,CHARINDEX('-',@TEL)-1-LEN(@A))+@A
END
RETURN @TMP
END
SELECT * FROM @TB
WHERE DBO.F_GETTEL(TEL,1)='05951202213' OR DBO.F_GETTEL(TEL,2)='05951202213'/*
TEL
------------------------------
0595-1202623-213
*/
--021-112-121
CREATE TABLE t1(TEL NVARCHAR(20))
INSERT INTO t1(TEL)
SELECT '010-110' UNION ALL
SELECT '021-112-121'GO DECLARE @s NVARCHAR(20)
SET @s='010110'
SELECT * FROM t1 WHERE CONVERT(NVARCHAR(20),Replace(TEL,'-',''))=@s
GO
DROP TABLE t1
GO
如果这样 replace(字段,'-','')='021112'肯定是不对的,怎么只判断第二个-号前的数值,如果有分机?不用like有办法没?
RETURNS VARCHAR(21)
AS
BEGIN
DECLARE @T VARCHAR(2)
WHILE PATINDEX('%[^0-9]%',@S) > 0
BEGIN
IF PATINDEX('+%',@S) = 1
BEGIN
SET @T='+'
SET @S=STUFF(@S,PATINDEX('%[^0-9]%',@S),1,'')
END
ELSE
SET @S=STUFF(@S,PATINDEX('%[^0-9]%',@S),1,'')
END
SET @S= @T + @S
RETURN @S
END
DROP FUNCTION DBO.TEL_NUMBER