SQLserver中字符串查找功能patindex和charindex的区别
最近经常使用字符串查找功能。 包括 1、全匹配查找字符串
2、模糊查找字符串 CHARINDEX 和 PATINDEX 函数都返回指定模式的开始位置。PATINDEX 可使用通配符,而 CHARINDEX 不可以。
这两个函数都带有2个参数:
1 希望获取其位置的模式。使用 PATINDEX,模式是可以包含通配符的字面字符串。使用 CHARINDEX,模式是字面字符串(不能包含通配符)。
2 字符串值表达式(通常为列名)。
例如,查找模式"wonderful"在 titles 表中 notes 列的某一特定行中的开始位置。
<div class="codetitle"><a style="CURSOR: pointer" data="78089" class="copybut" id="copybut78089" onclick="doCopy('code78089')"> 代码如下:
<div class="codebody" id="code78089">
USE pubs
SELECT CHARINDEX('wonderful',notes)
FROM titles
WHERE title_id = 'TC3218'
例如,使用
通配符查找模式"candies"在 Categories 表中的 Description 列的任一行中的开始位置:
<div class="codetitle">
<a style="CURSOR: pointer" data="69803" class="copybut" id="copybut69803" onclick="doCopy('code69803')"> 代码如下:
<div class="codebody" id="code69803">
USE Northwind
GO
SELECT CategoryID,PATINDEX('%candies%',Description)AS POSITION
FROM Categories
WHERE PATINDEX('%candies%',Description) <> 0
希望对大家有用。 如果上面仍然没看懂的,可以继续往下看:一、[
sql] patindex 详解 [Z]
PATINDEX
返回指定表达式中某模式第一次出现的起始位置;如果在全部有效的文本和字符数据类型中没有找到该模式,则返回零。
语法
PATINDEX ( "%pattern%",e-xpression )
参数
pattern
一个字符串。可以使用
通配符,但 pattern 之前和之后必须有 % 字符(
搜索第一个和最后一个字符时除外)。pattern 是短字符数据类型类别的表达式。
e-xpression
一个表达式,通常为要在其中
搜索指定模式的列,e-xpression 为字符串数据类型类别。
返回类型
int
注释
PATINDEX 对 text 数据类型很有用;除 IS NULL、IS NOT NULL 和 LIKE(这些是 Where 子句中对 text 类型有效的仅有的其它比较运算)外,PATINDEX 也可用于 Where 子句中。 ----------------------------------------- 例一:
找出Northwind.dbo.Categories表中Description字段中是包含单词“Bread”或“bread”的所有记录,那么选择语句就可能是这样: Select Description from Northwind.dbo.Categories
Where patindex("%[b,B]read%",description) > 0
PATINDEX 中可以使用
通配符来确定大写和小写的“b” 例二:
找出Northwind.dbo.Categories表中Description字段中是包含单词“Bread”或“bread”,且第二子字母不是“e”的记录。 select Description from Northwind.dbo.Categories
where patindex("%[b,description) > 0 and patindex("_[^e]%",description) = 1
通过在条件语句中
增加一个使用^
通配符的PATINDEX
函数,我们可以过滤掉“Dessert,candies,and sweet breads”这条记录。上面的
查询结果只有一条记录。
PATINDEX与CHARINDEX PATINDEX
函数支持使用
通配符,可以用在很多有变化的查找中。而CHARINDEX不可以。根据你自己不同的情况,这两个
函数对你在
sql Server中的字符串的
搜索、控制、分析很有帮助。
二、
sql Server CHARINDEX和PATINDEX详解
假如你写过很多程序,你可能偶尔会碰到要确定字符或字符窜串否包含在一段
文字中,在这篇
文章中,我将讨论使用CHARINDEX和PATINDEX
函数来
搜索文字列和字符串。我将告诉你这两个
函数是如何运转的,解释他们的区别。同时提供一些例子,通过这些例子,你可以可以考虑使用这两个
函数来
解决很多不同 的字符
搜索的问题。
CHARINDEX和PATINDEX
函数常常用来在一段字符中
搜索字符或者字符串。如果被
搜索的字符中包含有要
搜索的字符,那么这两个
函数返回一个非零 的整数,这个整数是要
搜索的字符在被
搜索的字符中的开始位数。PATINDEX
函数支持使用
通配符来进行
搜索,然而CHARINDEX
不支持通佩符。接下 来,我们逐个分析这两个
函数。 如何使用CHARINDEX
函数 CHARINDEX
函数返回字符或者字符串在另一个字符串中的起始位置。CHARINDEX
函数调用方法如下: CHARINDEX ( expression1,expression2 [,start_location ] ) Expression1是要到expression2中寻找的字符中,start_location是CHARINDEX
函数开始在expression2中找expression1的位置。 CHARINDEX
函数返回一个整数,返回的整数是要找的字符串在被找的字符串中的位置。假如CHARINDEX没有找到要找的字符串,那么
函数整数“0”。让我们看看下面的
函数命令执行的结果: CHARINDEX("
sql","Microsoft
sql Server") 这个
函数命令将返回在“Microsoft
sql Server”中“
sql”的起始位置,在这个例子中,CHARINDEX
函数将返回“S”在“Microsoft
sql Server”中的位置11。
接下来,我们看这个CHARINDEX命令: CHARINDEX("7.0","Microsoft
sql Server 2000") 在这个例子中,CHARINDEX返回零,因为字符串“7.0” 不能在“Microsoft
sql Server”中被找到。接下来通过两个例子来看看如何使用CHARINDEX
函数来
解决实际的T-
sql问题。 第一个例子,假设你要
显示Northwind
数据库Customer表前5行联系人列的Last Name。这是前5行数据
ContactName
------------------------------
Maria Anders
Ana Trujillo
Antonio Moreno
Thomas Hardy
Christina Berglund 你可以看到,CustomName包含客户的First Name和Last Name,它们之间被一个空格隔开。我用CHARINDX
函数确定两个名字中间空格的位置。通过这个
方法,我们可以分析ContactName列的空格位 置,这样我们可以只
显示这个列的last name部分。这是
显示Northwind的Customer表前5行last name的记录! select top 5 substring(ContactName,charindex(" ",ContactName)+1,
len(ContactName)) as [Last Name] from Northwind.dbo.customers 下面是这个命令
输出的结果。
Last Name
------------------------------
Anders
Trujillo
Moreno
Hardy
Berglund CHARINDEX
函数找到First Name和Last Name之间的空格,所以SUBSTRING
函数可以分开ContactName列,这样就只有Last Name被选出。我在CHARINDEX
函数返回的整数上加1,这样Last Name不是从空格开始。 在第二个例子中,即如说你要计算记录中,某一个字段包含特定字符的所有记录数。CHARINDEX
函数可以方便的
解决你的问题。计算 Northwind.dbo.Customer表中Addresses字段中包含单词Road或者它的缩写Rd的记录数,选择语句类似这样: SELECT count(*) from Northwind.dbo.Customers
WHERE CHARINDEX("Rd",Address) > 0 or CHARINDEX("Road",Address)> 1 如何使用PATINDEX
函数 PATINDEX
函数返回字符或者字符串在另一个字符串或者表达式中的起始位置,PATINDEX
函数支持搜索字符串中使用
通配符,这使PATINDEX
函数对于变化的
搜索字符串很有价值。PATINDEX
函数的命令如下: PATINDEX ( "%pattern%",expression ) Pattern是你要
搜索的字符串,expression是被
搜索的字符串。一般情况下expression是一个表中的一个字段,pattern的前后需要用“%”
标记,除非你
搜索的字符串在被收缩的字符串的最前面或者最后面。 和CHARINDEX
函数一样,PATINDEX
函数返回
搜索字符串在被
搜索字符串中的起始位置。假如有这样一个PATINDEX
函数: PATINDEX("%BC%","ABCD") 这个PATINDEX
函数返回的结果是2,这和CHARINDEX
函数一样。这里的%
标记告诉PATINDEX
函数去找字符串“BC”,不管被
搜索的字符串中在“BC”的前后有多少字符!
假如你想知道被
搜索字符串是否由特定的字符串开始,你可以省去前面的%
标记。PATINDED
函数就要这样写: PATINDEX("AB%","ABCD") 这个命令执行的结果返回1,表示
搜索的字符串“AB”在被
搜索的字符串中“ABCD”被找到。 使用
通配符可以编辑比我以上举得简单例子复杂得多的
搜索字符串。假如说你要确定一个字符串是否包含字母A和Z,还有任何数字,这个PARINDEX
函数命令可能像这样: PATINDEX("%[A,Z,0-9]%[A,0-9]%","XYZABC123") 注意在上面这个例子中的
搜索字符部分使用了很多的通陪符。察看
sql Server联机丛书可以获得更多关于通佩符的信息。接下来,我们用两个例子来看PATINDEX和SELECT怎么联合起来使用。 假设你想要找出Northwind.dbo.Categories表中Description字段中是包含单词“Bread”或“bread”的所有记录,那么选择语句就可能是这样: SELECT Description from Northwind.dbo.Categories
WHERE patindex("%[b,description) > 0 这里我用
通配符来确定大写和小写的“b”。我在Notthwind
数据库中执行这个脚本后,得到下面的结果:
Description
--------------------------------------------------------
Desserts,and sweet breads
Breads,crackers,pasta,and cereal 这是再用另外一个额外的
通配符来查找一些记录的例子。这个例子是如何选出上面的
查询结果中,Description字段的第二子字母不是“e”的纪录。 select Description from Northwind.dbo.Categories
where patindex("%[b,description) > 0
and patindex("_[^e]%",description) = 1 通过在条件语句中
增加一个使用^
通配符的PATINDEX
函数,我们可以过滤掉“Dessert,and sweet breads”这条记录。上面的
查询结果只有一条记录。
Description
--------------------------------------------------------
Breads,and cereal 总结 你现在可以发现CHARINDEX和PATINDEX
搜索字符串时的区别了吧。PATINDEX
函数支持使用
通配符,可以用在很多有变化的查找中。而 CHARINDEX不可以。根据你自己不同的情况,这两个
函数对你在
sql Server中的字符串的
搜索、控制、分析很有帮助。