SQL Server学习笔记之--与字符相关的问题

老爷子88

1.模式匹配
SQL server 提供的模式匹配是有限的，它利用LIKE 和PATINDEX 函数实现匹配，并不支持正则表达式。
通过收集字符列的子字符串统计信息，SQL2005加强了LIke的优化。即当字符串以通配符开始的时候，
它将比SQL2000更加精确的决定是否使用索引（意思就是以通配符开始的LIKE查询也是可以用索引的）。
-------函数LEFT查找以A开头的CustomerID
select companyname
from Customers
where LEFT(CustomerID,1)='A'
-----用like通配 查找以A开头的CustomerID
select companyname
from Customers
where CustomerID like 'A%'
通过执行计划（CTRL+L） 可以看到第一个表达式不再是SARG 优化器不再用有序的索引访问，即为 索引扫描
                                                  第二个表达式使用到了聚集索引查找
但是LIKE所能做到的匹配是很有限的，比如它不能很好涉及到重复的概念，
比如你要约束列col 使它只能输入数字
如果col列长度不长 只有8位 ，则 check（col like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'）
但是一旦长度不固定 你就可以这么做 check(col  like replicate('[0-9]',len(col ))) (replicate（a,b）返回B个重复的A)
但是当COL长度太长 上面句子效率太低 所以你可以使用否定的做法 即没有一个字符不是数字 check(sn not like '%[^0-9]%')
这里再说一个IP地址的问题
假如你要查找一个表里是正确的IP地址的IP ,比如
CREATE TABLE dbo.IPs(ip varchar(15) NOT NULL PRIMARY KEY);
--正确的IP地址
INSERT INTO dbo.IPs VALUES('131.107.2.201');
INSERT INTO dbo.IPs VALUES('131.33.2.201');
INSERT INTO dbo.IPs VALUES('131.33.2.202');
INSERT INTO dbo.IPs VALUES('3.107.2.4');
INSERT INTO dbo.IPs VALUES('3.107.3.169');
INSERT INTO dbo.IPs VALUES('3.107.104.172');
INSERT INTO dbo.IPs VALUES('22.107.202.123');
INSERT INTO dbo.IPs VALUES('22.20.2.77');
INSERT INTO dbo.IPs VALUES('22.156.9.91');
INSERT INTO dbo.IPs VALUES('22.156.89.32');
--不规范的IP地址
INSERT INTO dbo.IPs VALUES('1.1.1.256');
INSERT INTO dbo.IPs VALUES('1.1.1.1.1');
INSERT INTO dbo.IPs VALUES('1.1.1');
INSERT INTO dbo.IPs VALUES('1..1.1');
INSERT INTO dbo.IPs VALUES('.1.1.1');
INSERT INTO dbo.IPs VALUES('a.1.1.1');
GO
--根据IP地址的规范性进行控制where条件
select *
from IPs
where ip like '_%._%._%._%'--保证每个部分都不为NULL
and ip not like'%.%.%.%.%'--保证不存在4个以及更多点
and ip not like '%[0-9][0-9][0-9][0-9]%'--保证不会出现一个部分多于三个数的情况(每个部分最大255 8个字节)
and ip not like '%[^0-9.]%'--保证不出现数字和'.'外的字符
and ip not Like '%[3-9][0-9][0-9]%'--保证不出现300-999
and ip not like '%2[6-9][0-9]%'--保证不出现260-299
and ip not like '%25[6-9]%'--保证不出现256-259
--巧妙利用PAREENAME函数进行判断（这个可能我写的有漏洞）
select *
from IPs
where PARSENAME(ip,1) between 1 and 255 and
PARSENAME(ip,2) between 1 and 255 and
PARSENAME(ip,3) between 1 and 255 and
PARSENAME(ip,4) between 1 and 255  --这里没控制'.'因为PARSENAME只认识.
2.区分大小写
这个大小写的设置是依靠于数据的排序规则，默认是不区分的。
一般情况下 我们如果要在查询时候区分大小写时候 可以通过修改排序规则达到目的
USE Northwind;
go
SELECT CustomerID, CompanyName, Country
FROM dbo.Customers
WHERE CustomerID COLLATE Latin1_General_CS_AS = N'ALFKI';--这里通过COLLATE修改了排序规则后进行查询（CS 表示区分），这里关于SQL的排序规则可以在我的博客里找到相关详细介绍（http://blog.csdn.net/feixianxxx/archive/2009/10/05/4633610.aspx）
正是因为对列 CustomerID队了处理，那么它不再是一个SRAG，不会再使用到索引。
解决方法：
SELECT CustomerID, CompanyName, Country
FROM dbo.Customers
WHERE CustomerID COLLATE Latin1_General_CS_AS = N'ALFKI'
  AND CustomerID = N'ALFKI'; --这里添加了一个不区分大小写的判断语句，使得一开始能用索引查找相关的信息 然后再 过滤 速度自然快很多了
当然你也可以通过执行计划看到 第一个用到时扫描 第二个查找