to letsflytogether: 我的意思是:表(tbl_a)的 A 字段只能录入半角字符,我必须将录入了全角字符的记录找出来。用你的方法我要写很多语句,有更简单的方法吗?
区分大小写、全半角字符--例子,查大写字母 select * from( select aa='aa' union all select 'Aa' union all select 'AA' --全角A union all select 'A,A' --全角A,半角, union all select 'A,A' --全角A,全角, )a where aa collate Chinese_PRC_CS_AS_WS like '%A%'--查全角 select * from( select aa='aa' union all select 'Aa' union all select 'AA' --全角A union all select 'A,A' --全角A,半角, union all select 'A,A' --全角A,全角, )a where aa collate Chinese_PRC_CS_AS_WS like '%A%'--查半角, select * from( select aa='aa' union all select 'Aa' union all select 'AA' --全角A union all select 'A,A' --全角A,半角, union all select 'A,A' --全角A,全角, )a where aa collate Chinese_PRC_CS_AS_WS like '%,%'
select * from tableA where fldA collate Chinese_PRC_CI_AS_WS like '%A%' or fldA collate Chinese_PRC_CI_AS_WS like '%B%' ...
好笨的方法:select * from table where YourField collate Chinese_PRC_CS_AS_WS like '%[!"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~]¥%'
这个办法怎么样select * from( select aa='aa' union all select 'Aa' union all select 'AA' --全角A union all select 'A,A' --全角A,半角, union all select 'A,A' --全角A,全角, )a where datalength(aa)>len(aa)
select * from 你的表 where not exists(select 1 from ( select nchar((select count(*)+65280 from sysobjects where id<tem.id)) code from sysobjects tem) a where 你的列 like N'%'+code+N'%'
select * from 你的表 where not exists(select 1 from ( select nchar((select count(*)+65280 from sysobjects where id<tem.id)) code from sysobjects tem) a where 你的列 like N'%'+code+N'%')--or select * from 你的表 where not exists(select 1 from ( select nchar((select count(*)+65280 from sysobjects where id<tem.id)) code from sysobjects tem) a where charindex(code,你的列)>0)
Windows 排序规则名称
在 COLLATE 子句中指定 Windows 排序规则名称。Windows 排序规则名称由排序规则指示器和比较风格构成。语法
< Windows_collation_name > :: = CollationDesignator_<ComparisonStyle> < ComparisonStyle > ::=
CaseSensitivity_AccentSensitivity
[_KanatypeSensitive [_WidthSensitive ] ]
| _BIN参数
CollationDesignator指定 Windows 排序规则使用的基本排序规则。基本排序规则包括: 当指定按字典排序时应用其排序规则的字母表或语言
用于存储非 Unicode 字符数据的代码页。
例如 Latin1_General 或法文,两者都使用代码页 1252,或土耳其文,它使用代码页 1254。CaseSensitivityCI 指定不区分大小写,CS 指定区分大小写。AccentSensitivityAI 指定不区分重音,AS 指定区分重音。KanatypeSensitiveOmitted 指定不区分大小写,KS 指定区分假名类型。WidthSensitivityOmitted 指定不区分大小写,WS 指定区分大小写。BIN指定使用二进制排序次序。
我的意思是:表(tbl_a)的 A 字段只能录入半角字符,我必须将录入了全角字符的记录找出来。用你的方法我要写很多语句,有更简单的方法吗?
select * from(
select aa='aa'
union all select 'Aa'
union all select 'AA' --全角A
union all select 'A,A' --全角A,半角,
union all select 'A,A' --全角A,全角,
)a
where aa collate Chinese_PRC_CS_AS_WS like '%A%'--查全角
select * from(
select aa='aa'
union all select 'Aa'
union all select 'AA' --全角A
union all select 'A,A' --全角A,半角,
union all select 'A,A' --全角A,全角,
)a
where aa collate Chinese_PRC_CS_AS_WS like '%A%'--查半角,
select * from(
select aa='aa'
union all select 'Aa'
union all select 'AA' --全角A
union all select 'A,A' --全角A,半角,
union all select 'A,A' --全角A,全角,
)a
where aa collate Chinese_PRC_CS_AS_WS like '%,%'
select datalength('A')
fldA collate Chinese_PRC_CI_AS_WS like '%A%'
or fldA collate Chinese_PRC_CI_AS_WS like '%B%'
...
like '%[!"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~]¥%'
select aa='aa'
union all select 'Aa'
union all select 'AA' --全角A
union all select 'A,A' --全角A,半角,
union all select 'A,A' --全角A,全角,
)a
where datalength(aa)>len(aa)
select nchar((select count(*)+65280 from sysobjects where id<tem.id)) code from sysobjects tem) a
where 你的列 like N'%'+code+N'%'
select nchar((select count(*)+65280 from sysobjects where id<tem.id)) code from sysobjects tem) a
where 你的列 like N'%'+code+N'%')--or
select * from 你的表 where not exists(select 1 from (
select nchar((select count(*)+65280 from sysobjects where id<tem.id)) code from sysobjects tem) a
where charindex(code,你的列)>0)