一张表,内容如下:
编号 名称
0A-GT-090408-01C GT-M9804-023C
0A-GT-090408-01C-1N1 GT-M9804-023C-1-壓床
0A-GT-090408-01C-2E1 GT-M9804-023C-2-焊針
0A-GT-090409-01 GT-M9804-025
0A-GT-090409-01M GT-M9804-025模具
0A-GT-090409-02 GT-M9804-035
0A-GT-090410-01 GT-M9804-026
请问如何把不包含汉字的内容列出来,要求查询结果如下:
编号 名称
0A-GT-090408-01C GT-M9804-023C
0A-GT-090409-01 GT-M9804-025
0A-GT-090409-02 GT-M9804-035
0A-GT-090410-01 GT-M9804-026
编号 名称
0A-GT-090408-01C GT-M9804-023C
0A-GT-090408-01C-1N1 GT-M9804-023C-1-壓床
0A-GT-090408-01C-2E1 GT-M9804-023C-2-焊針
0A-GT-090409-01 GT-M9804-025
0A-GT-090409-01M GT-M9804-025模具
0A-GT-090409-02 GT-M9804-035
0A-GT-090410-01 GT-M9804-026
请问如何把不包含汉字的内容列出来,要求查询结果如下:
编号 名称
0A-GT-090408-01C GT-M9804-023C
0A-GT-090409-01 GT-M9804-025
0A-GT-090409-02 GT-M9804-035
0A-GT-090410-01 GT-M9804-026
--> Author : wufeng4552
--> Date : 2009-10-28
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (bh varchar(20),mc varchar(20))
insert into [tb]
select '0A-GT-090408-01C','GT-M9804-023C' union all
select '0A-GT-090408-01C-1N1','GT-M9804-023C-1-壓床' union all
select '0A-GT-090408-01C-2E1','GT-M9804-023C-2-焊針' union all
select '0A-GT-090409-01','GT-M9804-025' union all
select '0A-GT-090409-01M','GT-M9804-025模具' union all
select '0A-GT-090409-02','GT-M9804-035' union all
select '0A-GT-090410-01','GT-M9804-026'
select * from tb where len(mc)=datalength(mc)
/*
bh mc
-------------------- --------------------
0A-GT-090408-01C GT-M9804-023C
0A-GT-090409-01 GT-M9804-025
0A-GT-090409-02 GT-M9804-035
0A-GT-090410-01 GT-M9804-026*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-28 16:24:50
-- 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]([编号] varchar(20),[名称] varchar(20))
insert [tb]
select '0A-GT-090408-01C','GT-M9804-023C' union all
select '0A-GT-090408-01C-1N1','GT-M9804-023C-1-壓床' union all
select '0A-GT-090408-01C-2E1','GT-M9804-023C-2-焊針' union all
select '0A-GT-090409-01','GT-M9804-025' union all
select '0A-GT-090409-01M','GT-M9804-025模具' union all
select '0A-GT-090409-02','GT-M9804-035' union all
select '0A-GT-090410-01','GT-M9804-026'
--------------开始查询--------------------------
select * from tb t where not exists(select 1 from tb where PATINDEX('%[吖-做]%',名称)>0 and 名称=t.名称)
----------------结果----------------------------
/*编号 名称
-------------------- --------------------
0A-GT-090408-01C GT-M9804-023C
0A-GT-090409-01 GT-M9804-025
0A-GT-090409-02 GT-M9804-035
0A-GT-090410-01 GT-M9804-026(4 行受影响)
*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-28 16:24:50
-- 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]([编号] varchar(20),[名称] varchar(20))
insert [tb]
select '0A-GT-090408-01C','GT-M9804-023C' union all
select '0A-GT-090408-01C-1N1','GT-M9804-023C-1-壓床' union all
select '0A-GT-090408-01C-2E1','GT-M9804-023C-2-焊針' union all
select '0A-GT-090409-01','GT-M9804-025' union all
select '0A-GT-090409-01M','GT-M9804-025模具' union all
select '0A-GT-090409-02','GT-M9804-035' union all
select '0A-GT-090410-01','GT-M9804-026'
--------------开始查询--------------------------
select
*
from
tb t
where
not exists(select 1 from tb where PATINDEX('%[吖-做]%',名称)>0 and 名称=t.名称)
----------------结果----------------------------
/*编号 名称
-------------------- --------------------
0A-GT-090408-01C GT-M9804-023C
0A-GT-090409-01 GT-M9804-025
0A-GT-090409-02 GT-M9804-035
0A-GT-090410-01 GT-M9804-026(4 行受影响)
*/
go
create table [tb] (bh varchar(20),mc varchar(20))
insert into [tb]
select '0A-GT-090408-01C','GT-M9804-023C' union all
select '0A-GT-090408-01C-1N1','GT-M9804-023C-1-壓床' union all
select '0A-GT-090408-01C-2E1','GT-M9804-023C-2-焊針' union all
select '0A-GT-090409-01','GT-M9804-025' union all
select '0A-GT-090409-01M','GT-M9804-025模具' union all
select '0A-GT-090409-02','GT-M9804-035' union all
select '0A-GT-090410-01','GT-M9804-026'select * from tb where patindex('%[吖-座]%',mc)=0
/*
bh mc
-------------------- --------------------
0A-GT-090408-01C GT-M9804-023C
0A-GT-090409-01 GT-M9804-025
0A-GT-090409-02 GT-M9804-035
0A-GT-090410-01 GT-M9804-026(所影响的行数为 4 行)*/
go
create table [tb]([编号] varchar(20),[名称] varchar(20))
insert [tb]
select '0A-GT-090408-01C','GT-M9804-023C' union all
select '0A-GT-090408-01C-1N1','GT-M9804-023C-1-壓床' union all
select '0A-GT-090408-01C-2E1','GT-M9804-023C-2-焊針' union all
select '0A-GT-090409-01','GT-M9804-025' union all
select '0A-GT-090409-01M','GT-M9804-025模具' union all
select '0A-GT-090409-02','GT-M9804-035' union all
select '0A-GT-090410-01','GT-M9804-026'SELECT * FROM TB T WHERE PATINDEX('%[吖-做]%',名称)<=0
(所影响的行数为 7 行)编号 名称
-------------------- --------------------
0A-GT-090408-01C GT-M9804-023C
0A-GT-090409-01 GT-M9804-025
0A-GT-090409-02 GT-M9804-035
0A-GT-090410-01 GT-M9804-026(所影响的行数为 4 行)
ASCII 码 取汉字首字母 A-Z 基本可以包含所有汉字
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([编号] varchar(50),[名称] varchar(50))
insert [tb]
select '0A-GT-090408-01C','GT-M9804-023C' union all
select '0A-GT-090408-01C-1N1','GT-M9804-023C-1-壓床' union all
select '0A-GT-090408-01C-2E1','GT-M9804-023C-2-焊針' union all
select '0A-GT-090409-01','GT-M9804-025' union all
select '0A-GT-090409-01M','GT-M9804-025模具' union all
select '0A-GT-090409-02','GT-M9804-035' union all
select '0A-GT-090410-01','GT-M9804-026' union all
select '0Y-HS-20090624-2-5F1','HS-D0604076上片-5-電鍍' union all
select '0A-GT-090305-01A2','GT-M9802-064上章2' union all
select '0A-GT-090507-03-AB','C-47+1.4*9MM單圈' union all
select '0Y-HS-20090624-3-1-1W2','HS-D0604076-1下片-1-鋅壓鑄'--------------开始查询--------------------------
select
*
from
tb t
where
not exists(select 1 from tb where PATINDEX('%[吖-做]%',名称)>0 and 名称=t.名称)-------------执行结果-------------------------
编号 名称
-------------------------------------------------- --------------------------------------------------
0A-GT-090408-01C GT-M9804-023C
0A-GT-090409-01 GT-M9804-025
0A-GT-090409-02 GT-M9804-035
0A-GT-090410-01 GT-M9804-026
0Y-HS-20090624-2-5F1 HS-D0604076上片-5-電鍍
0A-GT-090305-01A2 GT-M9802-064上章2
0A-GT-090507-03-AB C-47+1.4*9MM單圈
0Y-HS-20090624-3-1-1W2 HS-D0604076-1下片-1-鋅壓鑄(8 row(s) affected)
select * from tb where PATINDEX('%[吖-做]%',名称)>0
select * from tb where PATINDEX('%[吖-做]%',名称)=0