declare @t table ( ID int, tel1 varchar(20), tel2 varchar(20)) insert @t select 1, '1234', '5678' union all select 2, '123', '5678' union all select 3, '1234', '567' union all select 4, '123', '123'Select ID, (Case When Len(Tel1) = 4 Then tel1 Else tel2 End) As Tel From @t Where Len(tel1) = 4 Or Len(tel2) = 4Select ID, (Case When Len(Tel2) = 4 Then tel2 Else tel1 End) As Tel From @t Where Len(tel1) = 4 Or Len(tel2) = 4
select * from a where len(tel1)=4 union select * from a where len(tel2)=4
select ID,case when LEN(tel1)=4 then tel1 else tel2 end as tel from a where LEN(tel1)=4 or LEN(tel2)=4 ID tel ----------- -------------------- 1 1234 2 5678 3 1234 select ID,case when LEN(tel2)=4 then tel2 else tel1 end as tel from a where LEN(tel1)=4 or LEN(tel2)=4 ID tel ----------- -------------------- 1 5678 2 5678 3 1234
select ID,tel1 from a where len(tel1)=4 union select ID,tel2 from a where len(tel2)=4
输入为 tel 啊 哥
-------------------------------------------------------------------------- -- Author : htl258(Tony) -- Date : 2010-04-27 22:27:35 -- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3) -- Blog : http://blog.csdn.net/htl258 -------------------------------------------------------------------------- --> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb] GO CREATE TABLE [tb]([id] INT,[tel1] INT,[tel2] INT) INSERT [tb] SELECT 1,1234,5678 UNION ALL SELECT 2,123,5678 UNION ALL SELECT 3,1234,567 UNION ALL SELECT 4,123,123 GO --SELECT * FROM [tb]-->SQL查询如下: select ID,case when len(tel1)<len(tel2) then tel2 else tel1 end tel from tb where len(tel1)=4 or len(tel2)=4 /* ID tel ----------- ----------- 1 1234 2 5678 3 1234(3 行受影响) */
ID int,
tel1 varchar(20),
tel2 varchar(20))
insert @t
select 1, '1234', '5678'
union all
select 2, '123', '5678'
union all
select 3, '1234', '567'
union all
select 4, '123', '123'Select ID, (Case When Len(Tel1) = 4 Then tel1 Else tel2 End) As Tel
From @t
Where Len(tel1) = 4 Or Len(tel2) = 4Select ID, (Case When Len(Tel2) = 4 Then tel2 Else tel1 End) As Tel
From @t
Where Len(tel1) = 4 Or Len(tel2) = 4
union
select * from a where len(tel2)=4
from a where LEN(tel1)=4 or LEN(tel2)=4 ID tel
----------- --------------------
1 1234
2 5678
3 1234
select ID,case when LEN(tel2)=4 then tel2 else tel1 end as tel
from a where LEN(tel1)=4 or LEN(tel2)=4
ID tel
----------- --------------------
1 5678
2 5678
3 1234
union
select ID,tel2 from a where len(tel2)=4
输入为 tel 啊 哥
-- Author : htl258(Tony)
-- Date : 2010-04-27 22:27:35
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[tel1] INT,[tel2] INT)
INSERT [tb]
SELECT 1,1234,5678 UNION ALL
SELECT 2,123,5678 UNION ALL
SELECT 3,1234,567 UNION ALL
SELECT 4,123,123
GO
--SELECT * FROM [tb]-->SQL查询如下:
select ID,case when len(tel1)<len(tel2) then tel2 else tel1 end tel
from tb
where len(tel1)=4 or len(tel2)=4
/*
ID tel
----------- -----------
1 1234
2 5678
3 1234(3 行受影响)
*/