XinXiangXLX_API/文档/Sql/sql_20190820...

--SELECT  *
--  FROM [CallCenter_XinXiangXLX].[dbo].[T_Sys_MobileData]
--  where F_ZipCode like '01%' --F_CityDes like '%新疆%' or F_CityDes like '%九江%'
  
  declare @tel nvarchar(20) 
  declare @tellen int
  declare @telstr nvarchar(20)
  declare @code3 nvarchar(20) declare @code4 nvarchar(20)
  declare @dxx int --新乡
  declare @dxj int --新疆
  declare @djj int --九江
  declare @res nvarchar(5)
  
  set @tel='02156984887'
  --select datalength(@tel) as datalength长度, len(@tel) as len长度
  set @tellen=LEN(@tel)
  --select @tellen
  if @tellen=11
  begin
	if SUBSTRING(@tel,1,1)=1
	begin
		set @telstr=LEFT(@tel,7)
	end
	else
	begin
		set @code3=SUBSTRING(@tel,1,3)
		set @code4=SUBSTRING(@tel,1,4)
	end
  end
  else
  begin
	if @tellen>11
	begin
		if SUBSTRING(@tel,1,1)=0
		begin
			set @telstr=SUBSTRING(@tel,2,7)
			set @code4=SUBSTRING(@tel,1,4)
		end
		else
		begin
			set @code4=SUBSTRING(@tel,1,4)
		end
	end
  end
  
SELECT  @dxj=COUNT(1) FROM [CallCenter_XinXiangXLX].[dbo].[T_Sys_MobileData] where (F_MobileNum=@telstr  or F_ZipCode=@code3 or F_ZipCode=@code4) and F_CityDes like '%新疆%'
SELECT  @djj=COUNT(1) FROM [CallCenter_XinXiangXLX].[dbo].[T_Sys_MobileData] where (F_MobileNum=@telstr  or F_ZipCode=@code3 or F_ZipCode=@code4)  and F_CityDes like '%九江%'
SELECT  @dxx=COUNT(1) FROM [CallCenter_XinXiangXLX].[dbo].[T_Sys_MobileData] where (F_MobileNum=@telstr  or F_ZipCode=@code3 or F_ZipCode=@code4)  and F_CityDes like '%新乡%'
  
select @tel,SUBSTRING(@tel,1,1),@tellen,@code3,@code4,@telstr,@dxj,@djj,@dxx

if @dxx=1
begin
	set @res='xx'
end
if @dxj=1
begin
	set @res='xj'
end
if @djj=1
begin
	set @res='jj'
end

select isnull(@res,'')