patindex与stuff函数之用法

今天突然想写一个查找数字的函数？记得之间有chaindex函数判断是否存在字符，C# JS等语言中有正则表达式，那SQL中有木有呢？那就先来学习语法把

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 子句中。

思路：必须先删除后添加字符串 用函数stuff

STUFF

STUFF ( character_expression , start , length ,character_expression )

参数

character_expression

一个字符数据表达式。character_expression可以是常量、变量，也可以是字符列或二进制数据列。

start

一个整数值，指定删除和插入的开始位置。如果start或 length为负，则返回空字符串。如果start比第一个 character_expression长，则返回空字符串。start可以是 bigint 类型。

length

一个整数，指定要删除的字符数。如果length比第一个character_expression长，则最多删除到最后一个character_expression中的最后一个字符。length可以是bigint类型。

返回类型

　　如果character_expression是受支持的字符数据类型，则返回字符数据。如果character_expression是一个受支持的binary数据类型，则返回二进制数据。

备注

　　如果开始位置或长度值是负数，或者如果开始位置大于第一个字符串的长度，将返回空字符串。如果要删除的长度大于第一个字符串的长度，将删除到第一个字符串中的第一个字符。
如果结果值大于返回类型支持的最大值，则产生错误。

示例

　　以下示例在第一个字符串abcdef中删除从第2个位置（字符 b）开始的三个字符，然后在删除的起始位置插入第二个字符串，从而创建并返回一个字符串。

1SELECT STUFF('abcdef', 2, 3, 'ijklmn'); 2GO

　　下面是结果集：

aijklmnef (1 row(s) affected)

那就写写吧：

--===================================== --功能: 获取字符串中的字母 --日期:2011-07-28 龚德辉 --===================================== ALTER FUNCTION [dbo].[F_Get_STR] (@S VARCHAR(100)) RETURNS VARCHAR(100) AS BEGIN WHILE PATINDEX('%[^a-zA-Z]%',@S)>0 BEGIN set @S=stuff(@S,patindex('%[^a-zA-Z]%',@S),1,'') END RETURN @S END --===================================== --功能: 获取字符串中的字母 --日期: 2011-07-28龚德辉 --===================================== ALTER function [dbo].[F_Get_Number] ( @S varchar(100) ) returns int AS begin while PATINDEX('%[^0-9]%',@S)>0 begin set @S=stuff(@S,patindex('%[^0-9]%',@S),1,'') end return cast(@S as int) end --测试 ---select dbo.F_Get_Number('测试AB3C123AB5C') set nocount on declare @t table([Name] nvarchar(100)) insert @t select N'地，地387道,道' insert @t select N'地地387道。道' insert @t select N'地地道道' insert @t select N'地，地387道,SDSD' insert @t select N'123' select * from @t where patindex('%[0-9]%',[Name])>0 --包含數字 select * from @t where patindex('%[吖-座]%',[Name])>0 --包含漢字 select * from @t where patindex('%[a-zA-Z]%',[Name])>0 --包含字母

以下是网上找的几个判断函数，原理都一样

通过2个函数CHARINDEX和PATINDEX以及通配符的灵活使用
函数：CHARINDEX和PATINDEX
CHARINDEX：查某字符(串)是否包含在其他字符串中，返回字符串中指定表达式的起始位置。
PATINDEX：查某字符(串)是否包含在其他字符串中，返回指定表达式中某模式第一次出现的起始位置；如果在全部有效的文本和字符数据类型中没有找到该模式，则返回零。特殊：可以使用通配符！

例子：
1. 查询字符串中是否包含非数字字符

SELECT PATINDEX('%[^0-9]%', '1235X461') SELECT PATINDEX('%[^0-9]%', '12350461')

2. 查询字符串中是否包含数字字符

SELECT PATINDEX('%[0-9]%', 'SUYLLGoO') SELECT PATINDEX('%[0-9]%', 'SUYLLG0O')

3.函数判断字符串只包含数字

CREATE FUNCTION [dbo].fn_IsNumeric ( @pString VARCHAR(8000) ) RETURNS bit WITH ENCRYPTION AS BEGIN DECLARE @vJudge int SET @vJudge = 0 SELECT @vJudge = CASE WHEN PATINDEX('%[0-9]%', LOWER(@pString)) > 0 THEN 0 WHEN PATINDEX('%[0-9]%', LOWER(@pString)) = 0 THEN 1 END RETURN @vJudge END

4.函数判断字符串只包含字母（忽略大小写）

CREATE FUNCTION [dbo].fn_IsAlpha ( @pString VARCHAR(8000) ) RETURNS bit WITH ENCRYPTION AS BEGIN DECLARE @vJudge int SET @vJudge = 0 SELECT @vJudge = CASE WHEN PATINDEX('%[a-z]%', LOWER(@pString)) > 0 THEN 0 WHEN PATINDEX('%[a-z]%', LOWER(@pString)) = 0 THEN 1 END RETURN @vJudge END

5. 函数判断字符串不包含任何符号（包括空格）

CREATE FUNCTION [dbo].fn_IsAlphanumeric ( @pString VARCHAR(8000) ) RETURNS bit WITH ENCRYPTION AS BEGIN DECLARE @vJudge int SET @vJudge = 0 SELECT @vJudge = CASE WHEN PATINDEX('%[^a-z0-9]%', LOWER(@pString)) > 0 THEN 0 WHEN PATINDEX('%[^a-z0-9]%', LOWER(@pString)) = 0 THEN 1 END RETURN @vJudge END 6. 函数判断字符串不包含任何符号（除空格外） CREATE FUNCTION [dbo].fn_IsAlphanumericBlank ( @pString VARCHAR(8000) ) RETURNS bit WITH ENCRYPTION AS BEGIN DECLARE @vJudge int SET @vJudge = 0 SELECT @vJudge = CASE WHEN PATINDEX('%[^a-z0-9 ]%', LOWER(@pString)) > 0 THEN 0 WHEN PATINDEX('%[^a-z0-9 ]%', LOWER(@pString)) = 0 THEN 1 END RETURN @vJudge END

-- 注意：[^a-z0-9 ]模式中最后有一个空格。

用charindex（）——charindex（字符，字符串）>0 –>包含