--1.查询区分全角与半角字符 --测试数据 DECLARE @t TABLE(col varchar(10)) INSERT @t SELECT 'aa' UNION ALL SELECT 'Aa' UNION ALL SELECT 'AA' --全角A UNION ALL SELECT 'A,A' --全角A,半角逗号(,) UNION ALL SELECT 'A,A' --全角A,全角逗号(,)--1.查大写字母 SELECT * FROM @t WHERE col COLLATE Chinese_PRC_CS_AS_WS like '%A%'--2.查全角字母 SELECT * FROM @t WHERE col COLLATE Chinese_PRC_CS_AS_WS like '%A%'--3.查半角逗号(,) SELECT * FROM @t WHERE col COLLATE Chinese_PRC_CS_AS_WS like '%,%'--3.查全角逗号(,) SELECT * FROM @t WHERE col COLLATE Chinese_PRC_CS_AS_WS like '%,%' GO /*=============================================*/ --2 实现全角与半角字符转换的处理函数 CREATE FUNCTION f_Convert( @str NVARCHAR(4000), --要转换的字符串 @flag bit --转换标志,0转换成半角,1转换成全角 )RETURNS nvarchar(4000) AS BEGIN DECLARE @pat nvarchar(8),@step int,@i int,@spc int IF @flag=0 SELECT @pat=N'%[!-~]%',@step=-65248, @str=REPLACE(@str,N' ',N' ') ELSE SELECT @pat=N'%[!-~]%',@step=65248, @str=REPLACE(@str,N' ',N' ') SET @i=PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN,@str) WHILE @i>0 SELECT @str=REPLACE(@str, SUBSTRING(@str,@i,1), NCHAR(UNICODE(SUBSTRING(@str,@i,1))+@step)) ,@i=PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN,@str) RETURN(@str) END GO
/* 在库TEST上建立全文索引 */ use test create table poofly(id int not null, name varchar(10)) go /* 首先创建一个唯一索引,以便全文索引利用*/ create unique clustered index un_ky1 on poofly(id) /*创建全文目录*/ create FULLTEXT CATALOG FT1 AS DEFAULT /*C创建全文索引*/ create FULLTEXT INDEX ON poofly(NAME) key index un_ky1 ON FT1 /*修改全文目录*/ alter FULLTEXT CATALOG FT1 REBUILD /*删除全文目录FT(含有全文索引时候不能删除)*/ drop fulltext catalog ft /*查看数据库所有的全文目录*/ select* from sys.fulltext_catalogs /* fulltext_catalog_id name path is_default is_accent_sensitivity_on data_space_id file_id principal_id is_importing ------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ------------------------ ------------- ----------- ------------ ------------ 5 test NULL 0 1 NULL NULL 1 0 11 FT1 NULL 1 1 NULL NULL 1 0 */ /* 查看所有用到全文索引的表*/ exec sp_help_fulltext_tables /* TABLE_OWNER TABLE_NAME FULLTEXT_KEY_INDEX_NAME FULLTEXT_KEY_COLID FULLTEXT_INDEX_ACTIVE FULLTEXT_CATALOG_NAME -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------ --------------------- -------------------------------------------------------------------------------------------------------------------------------- dbo poofly un_ky1 1 1 FT1 */ /* 在库TEST上建立全文索引 */ use test create table poofly(id int not null, name varchar(10)) go /* 首先创建一个唯一索引,以便全文索引利用*/ create unique clustered index un_ky1 on poofly(id) /*创建全文目录*/ create FULLTEXT CATALOG FT1 AS DEFAULT /*C创建全文索引*/ create FULLTEXT INDEX ON poofly(NAME) key index un_ky1 ON FT1 /*修改全文目录*/ alter FULLTEXT CATALOG FT1 REBUILD /*删除全文目录FT(含有全文索引时候不能删除)*/ drop fulltext catalog ft --------------------------------------------------- --使用contains关键字进行全文索引--1.前缀搜索 select name from tb where contains(name,'"china*"') /*--注意这里的* 返回结果会是 chinax chinay chinaname china --返回前缀是china的name --如果不用“”隔开 那么系统会都城 contains(name,'china*') 与china* 匹配*/--2.使用派生词搜索 select name from tb where contains(name,'formsof(inflectional,"foot")') /* 出来结果可能是 foot feet (所有动词不同形态 名词单复数形式)*/--3.词加权搜索 select value from tb where contains(value , 'ISABOUT(performance weight(.8))') /*全值用0-1的一个数字表示 表示每个词的重要程度*/--4.临近词搜素 select * from tb where contains(document,'a near b') /* 出来的结果是“a”单词与“b”单词临近的document 可以写成 contains(document,'a ~ b') */ --5.布尔逻辑搜素 select * from tb where contains(name,'"a" and "b"') /*返回既包含A 又包含 B单词的行 当然 这里的AND 关键字还有换成 OR ,AND NOT 等 */ -------------------------------------------------- --你还可以使用RREETEXT 进行模糊搜索 --任意输入文本 全文索引自动识别重要单词 然后构造一个查询 use test go select * from tb where freetext(wendang,'zhubajie chi xi gua !')
检索出含有全角数字的记录 select * from tb where data COLLATE CHINESE_PRC_CS_AS_KS_WS LIKE '%[0-9]%'; 更新就不用说了,update 就可以了
用自定义函数替换SQL中的全角数字为半角数字2007年12月26日 星期三 上午 11:23在我们使用MSSQL的时候,有时表中有一些是全角的数字,但我们的系统不能够正确的使用它们,这时我们要把他们替换为半角数字。而SQL自己好像没有这个函数。没有,我们自己动手:下面就是我自己写的一个函数,当然,我的数据量小,没有考虑性能!-- 把全角数据转换成半角数字(注意,这个只能转换全是全角的,在第一个半角数据时返回) -- 公司:石家庄创联科技 -- 编写:牛昆亮 QQ:273352165create function ufn_convertWideNumericToAnsi ( @vstrIn varchar(1000) ) returns varchar(4000) as begin declare @strReturn varchar(4000) ,@bin varbinary(4000) ,@str varchar(4000) ,@stmp varchar(4) ,@i int ,@len int -- ,@vstrIn varchar(1000)--set @vstrIn ='031851001845' set @strReturn='' set @bin=convert(varbinary(4000),@vstrIn) exec master..xp_varbintohexstr @bin, @str outselect @str=stuff(@str,1,2,'') set @len=len(@str) set @i=1 while @i<@len begin set @stmp = substring(@str,@i,4) if(substring(@stmp,1,1) <> 'A') return @vstrIn set @stmp = replace(@stmp,'A','') set @stmp = replace(@stmp,'B','') --print @stmp set @stmp = cast((convert(int,@stmp)-30) as varchar(1)) set @strReturn = @strReturn + @stmp set @i=@i+4 end --print @strReturn return @strReturn end示例select '031851001845' as ORG, dbo.ufn_convertWideNumericToAnsi('031851001845') DESORG DES ------------------------ --------------------------- 031851001845 031851001845 网站上找的
--找到记录 select * from tb where PATINDEX ( '%[1234567890]%' , colname COLLATE CHINESE_PRC_CS_AS_KS_WS )>0
--测试数据
DECLARE @t TABLE(col varchar(10))
INSERT @t SELECT 'aa'
UNION ALL SELECT 'Aa'
UNION ALL SELECT 'AA' --全角A
UNION ALL SELECT 'A,A' --全角A,半角逗号(,)
UNION ALL SELECT 'A,A' --全角A,全角逗号(,)--1.查大写字母
SELECT * FROM @t
WHERE col COLLATE Chinese_PRC_CS_AS_WS like '%A%'--2.查全角字母
SELECT * FROM @t
WHERE col COLLATE Chinese_PRC_CS_AS_WS like '%A%'--3.查半角逗号(,)
SELECT * FROM @t
WHERE col COLLATE Chinese_PRC_CS_AS_WS like '%,%'--3.查全角逗号(,)
SELECT * FROM @t
WHERE col COLLATE Chinese_PRC_CS_AS_WS like '%,%'
GO
/*=============================================*/
--2 实现全角与半角字符转换的处理函数
CREATE FUNCTION f_Convert(
@str NVARCHAR(4000), --要转换的字符串
@flag bit --转换标志,0转换成半角,1转换成全角
)RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @pat nvarchar(8),@step int,@i int,@spc int
IF @flag=0
SELECT @pat=N'%[!-~]%',@step=-65248,
@str=REPLACE(@str,N' ',N' ')
ELSE
SELECT @pat=N'%[!-~]%',@step=65248,
@str=REPLACE(@str,N' ',N' ')
SET @i=PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN,@str)
WHILE @i>0
SELECT @str=REPLACE(@str,
SUBSTRING(@str,@i,1),
NCHAR(UNICODE(SUBSTRING(@str,@i,1))+@step))
,@i=PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN,@str)
RETURN(@str)
END
GO
在库TEST上建立全文索引
*/
use test
create table poofly(id int not null, name varchar(10))
go
/* 首先创建一个唯一索引,以便全文索引利用*/
create unique clustered index un_ky1 on poofly(id)
/*创建全文目录*/
create FULLTEXT CATALOG FT1 AS DEFAULT
/*C创建全文索引*/
create FULLTEXT INDEX ON poofly(NAME) key index un_ky1 ON FT1
/*修改全文目录*/
alter FULLTEXT CATALOG FT1 REBUILD
/*删除全文目录FT(含有全文索引时候不能删除)*/
drop fulltext catalog ft
/*查看数据库所有的全文目录*/
select* from sys.fulltext_catalogs
/*
fulltext_catalog_id name path is_default is_accent_sensitivity_on data_space_id file_id principal_id is_importing
------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ------------------------ ------------- ----------- ------------ ------------
5 test NULL 0 1 NULL NULL 1 0
11 FT1 NULL 1 1 NULL NULL 1 0
*/
/* 查看所有用到全文索引的表*/
exec sp_help_fulltext_tables
/*
TABLE_OWNER TABLE_NAME FULLTEXT_KEY_INDEX_NAME FULLTEXT_KEY_COLID FULLTEXT_INDEX_ACTIVE FULLTEXT_CATALOG_NAME
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------ --------------------- --------------------------------------------------------------------------------------------------------------------------------
dbo poofly un_ky1 1 1 FT1
*/
/*
在库TEST上建立全文索引
*/
use test
create table poofly(id int not null, name varchar(10))
go
/* 首先创建一个唯一索引,以便全文索引利用*/
create unique clustered index un_ky1 on poofly(id)
/*创建全文目录*/
create FULLTEXT CATALOG FT1 AS DEFAULT
/*C创建全文索引*/
create FULLTEXT INDEX ON poofly(NAME) key index un_ky1 ON FT1
/*修改全文目录*/
alter FULLTEXT CATALOG FT1 REBUILD
/*删除全文目录FT(含有全文索引时候不能删除)*/
drop fulltext catalog ft
---------------------------------------------------
--使用contains关键字进行全文索引--1.前缀搜索
select name from tb where contains(name,'"china*"')
/*--注意这里的* 返回结果会是 chinax chinay chinaname china
--返回前缀是china的name
--如果不用“”隔开 那么系统会都城 contains(name,'china*') 与china* 匹配*/--2.使用派生词搜索
select name from tb where contains(name,'formsof(inflectional,"foot")')
/* 出来结果可能是 foot feet (所有动词不同形态 名词单复数形式)*/--3.词加权搜索
select value from tb where contains(value , 'ISABOUT(performance weight(.8))')
/*全值用0-1的一个数字表示 表示每个词的重要程度*/--4.临近词搜素
select * from tb where contains(document,'a near b')
/* 出来的结果是“a”单词与“b”单词临近的document
可以写成 contains(document,'a ~ b')
*/
--5.布尔逻辑搜素
select * from tb where contains(name,'"a" and "b"')
/*返回既包含A 又包含 B单词的行
当然 这里的AND 关键字还有换成 OR ,AND NOT 等
*/
--------------------------------------------------
--你还可以使用RREETEXT 进行模糊搜索
--任意输入文本 全文索引自动识别重要单词 然后构造一个查询
use test
go
select * from tb where freetext(wendang,'zhubajie chi xi gua !')
select * from tb where data COLLATE CHINESE_PRC_CS_AS_KS_WS LIKE '%[0-9]%';
更新就不用说了,update 就可以了
-- 公司:石家庄创联科技
-- 编写:牛昆亮 QQ:273352165create function ufn_convertWideNumericToAnsi
(
@vstrIn varchar(1000)
)
returns varchar(4000)
as
begin
declare @strReturn varchar(4000)
,@bin varbinary(4000)
,@str varchar(4000)
,@stmp varchar(4)
,@i int
,@len int
-- ,@vstrIn varchar(1000)--set @vstrIn ='031851001845'
set @strReturn=''
set @bin=convert(varbinary(4000),@vstrIn)
exec master..xp_varbintohexstr @bin, @str outselect @str=stuff(@str,1,2,'')
set @len=len(@str)
set @i=1
while @i<@len
begin
set @stmp = substring(@str,@i,4)
if(substring(@stmp,1,1) <> 'A')
return @vstrIn
set @stmp = replace(@stmp,'A','')
set @stmp = replace(@stmp,'B','')
--print @stmp
set @stmp = cast((convert(int,@stmp)-30) as varchar(1))
set @strReturn = @strReturn + @stmp
set @i=@i+4
end
--print @strReturn
return @strReturn
end示例select '031851001845' as ORG, dbo.ufn_convertWideNumericToAnsi('031851001845') DESORG DES
------------------------ ---------------------------
031851001845 031851001845
网站上找的
select * from tb where PATINDEX ( '%[1234567890]%' , colname COLLATE CHINESE_PRC_CS_AS_KS_WS )>0
转换说明
全角字符从的unicode编码从65281~65374
半角字符从的unicode编码从 33~126
空格比较特殊,全角为 12288,半角为 32
而且除空格外,全角/半角按unicode编码排序在顺序上是对应的
所以可以直接通过用+-法来处理非空格数据,对空格单独处理
like的时候,指定排序规则 COLLATE Latin1_General_BIN
是保证字符顺序按unicode编码排序
(此函数部分思路参考了CSDN上大力的转换函数)
--邹建 2005.01(引用请保留此信息)--*/
/*--调用示例
declare @s1 varchar(8000)
select @s1='中 2-3456a78STUVabn中国opwxyz'
select dbo.f_convert(@s1,0),dbo.f_convert(@s1,1)
--*/
create FUNCTION [dbo].[fn_Convert](
@str NVARCHAR(500), --要转换的字符串
@flag bit --转换标志,0转换成半角,1转换成全角
)RETURNS nvarchar(500)
AS
BEGIN
DECLARE @pat nvarchar(8),@step int,@i int,@spc int
IF @flag=0
SELECT @pat=N'%[!-~]%',@step=-65248,
@str=REPLACE(@str,N' ',N' ')
ELSE
SELECT @pat=N'%[!-~]%',@step=65248,
@str=REPLACE(@str,N' ',N' ')
SET @i=PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN,@str)
WHILE @i>0
SELECT @str=REPLACE(@str,
SUBSTRING(@str,@i,1),
NCHAR(UNICODE(SUBSTRING(@str,@i,1))+@step))
,@i=PATINDEX(@pat COLLATE LATIN1_GENERAL_BIN,@str)
RETURN(@str)
END