Synear_API/sql/sql_GetPhone...

/****** Object:  UserDefinedFunction [dbo].[GetDeptName]    Script Date: 05/17/2018 17:40:36 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetPhoneLocation]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[GetPhoneLocation]
GO

/****** Object:  UserDefinedFunction [dbo].[GetDeptName]    Script Date: 05/17/2018 17:40:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





CREATE FUNCTION [dbo].[GetPhoneLocation] (
   @Caller NVARCHAR(50)
)  

RETURNS varchar(50)
AS  
BEGIN 
	
	declare @areacode nvarchar(50)--区号

	if LEN(@Caller)=11 and @Caller LIKE '[1][3456789]%'
	begin
		--处理主叫号码 获取手机号码前7位
			declare @Top7 char(10)
			
			set @Top7=SubString(@Caller, 1,7);
			
			--根据前7位获取区号
			select @areacode=F_ZipCode FROM T_Sys_MobileData where F_MobileNum=@Top7
			
	end
	else
	begin
		--固定电话获取归属地
		if LEN(@Caller)>10
		begin
			declare @three char(5)
			declare @four char(5)
			declare @cc int
			
			set @three=LEFT(@Caller, 3);
			set @four=LEFT(@Caller, 4);
			
			--根据区号获取地区ID
			select @cc=COUNT(1) FROM tel_location where tel=@four
			
			if @cc>0
			begin
				set @areacode=@four
			end
			else
			begin
				set @areacode= @three
			end
		end
		else
		begin --号码小于10位默认为本地固定电话，默认区号为0371
			set @areacode='0371'
		end
	end
	return @areacode
END






GO