表如下:
id | NO | name |
1 | 1.1 | **** |
2 | 1.1.1 | ***** |
3 | 1.1.1.1 | **** |
4 | 11.11 | ****** |
5 | 11.11.1 | *** |
:
:
这样的表中,我要查询"NO"字段有且只有一个“.”的数据,请问select语句怎么写?
id | NO | name |
1 | 1.1 | **** |
2 | 1.1.1 | ***** |
3 | 1.1.1.1 | **** |
4 | 11.11 | ****** |
5 | 11.11.1 | *** |
:
:
这样的表中,我要查询"NO"字段有且只有一个“.”的数据,请问select语句怎么写?
select * from tb
where charindex('.',stuff(NO,charindex('.',NO),1,''))=0
CREATE TABLE T2(no varchar(10))
INSERT T2
SELECT '1.1' UNION ALL
SELECT '2.1.1' UNION ALL
SELECT '3.1'select * from t2
where charindex('.',stuff(NO,charindex('.',NO),1,''))=0
no
----------
1.1
3.1(2 行受影响)DROP TABLE T2
where len(replace(no,'.',''))=1
...
where CHARINDEX('.',no) > 0 and charindex('.',substring(no,CHARINDEX('.',no) + 1,len(no)- CHARINDEX('.',no) - 1)) = 0
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-29 16:55:33
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[NO] varchar(7),[name] varchar(6))
insert [tb]
select 1,'1.1','****' union all
select 2,'1.1.1','*****' union all
select 3,'1.1.1.1','****' union all
select 4,'11.11','******' union all
select 5,'11.11.1','***'
--------------开始查询--------------------------
select * from TB where len(NO)-len(replace(no,'.',''))=1
----------------结果----------------------------
/* ----------- ------- ------
1 1.1 ****
4 11.11 ******(2 行受影响)
*/
CREATE TABLE T2(no varchar(10))
INSERT T2
SELECT '1.1' UNION ALL
SELECT '2.1.1' UNION ALL
SELECT '3.1'select * from t2
where len(no)-len(replace(no,'.',''))=1/*
no
----------
1.1
3.1
*/
--我是烤鸭,无耻地抄袭小F的数据
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-29 16:55:33
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[NO] varchar(7),[name] varchar(6))
insert [tb]
select 1,'1.1','****' union all
select 2,'1.1.1','*****' union all
select 3,'1.1.1.1','****' union all
select 4,'11.11','******' union all
select 5,'11.11.1','***'
--------------开始查询--------------------------
SELECT * FROM TB
WHERE CHARINDEX('.',[NO],(CHARINDEX('.',[NO])+1))=0
----------------结果----------------------------
/* ----------- ------- ------
1 1.1 ****
4 11.11 ******(2 行受影响)
*/
----------------------------------------------------------------
--我是烤鸭,无耻地抄袭小F的数据
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-29 16:55:33
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[NO] varchar(7),[name] varchar(6))
insert [tb]
select 1,'1.1','****' union all
select 2,'1.1.1','*****' union all
select 3,'1.1.1.1','****' union all
select 4,'11.11','******' union all
select 5,'11.11.1','***'
--------------开始查询--------------------------
select * from TB where LENGTH(NO)-LENGTH(replace(no,'.',''))=1
----------------结果----------------------------
/* ----------- ------- ------
1 1.1 ****
4 11.11 ******(2 行受影响)
*/