SQL常用的Function

r囧r小猫 2022-01-07 00:13 224阅读 0赞

SET QUOTED\_IDENTIFIER ON

SET ANSI\_NULLS ON

/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

\* Name: 把数字生成固定位数的字符串（字符串）

\* Author:　Sman huang

\* ALTER Date: 2007-8-13

\* ALTER\_Description:

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/

ALTER FUNCTION dbo.ufc\_LeftPadding(@VAL NVARCHAR(50), @LENGTH INT)

RETURNS NVARCHAR(50)

BEGIN

DECLARE @RET AS NVARCHAR(50)

select @RET=RIGHT( '00000000000000' +CONVERT(NVARCHAR(50),@VAL),@LENGTH)

RETURN @RET

END

SET QUOTED\_IDENTIFIER OFF

SET ANSI\_NULLS ON

SET QUOTED\_IDENTIFIER OFF

SET ANSI\_NULLS OFF

\--select \* from dbo.ufc\_Split('1,tt,tt;2,ss,ss;',';')

/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

\* Name: T-SQL Split算法实现（整型）

\* Author:　Sman Huang

\* ALTER Date:

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/

ALTER Function dbo.ufc\_Split

(

@InputStr varchar(500),

@SplitChar VARCHAR(1)

)

Returns @ufc\_Split table

(

val varchar(50)

)

Begin

Declare @str varchar(250)

Declare @substr varchar(250)

Declare @iLen int

Declare @iStart int

set @str=RTrim(Ltrim(@InputStr))

set @iStart=CHARINDEX( @SplitChar , @str )

set @iLen=Len( @str )

IF @iStart>0

Begin

set @substr=substring( @str , 1 , @iStart-1 )

set @str=substring( @str , @iStart+1 , @iLen-@iStart )

End

Else

Begin

set @substr=@str

set @str=''

End

set @substr=RTRIM( LTRIM( @substr ) )

insert @ufc\_Split select id=cast( @substr as VARCHAR(50) )

While Len( @str )>0

Begin

\---------------- Loop Begin ---------------

set @iStart=CHARINDEX( @SplitChar , @str )

set @iLen=Len( @str )

IF @iStart>0

Begin

set @substr=substring( @str , 1 , @iStart-1 )

set @str=substring( @str , @iStart+1 , @iLen-@iStart )

End

Else

Begin

set @substr=@str

set @str=''

End

set @substr=RTRIM( LTRIM( @substr ) )

insert @ufc\_Split select id=cast( @substr as VARCHAR(50) )

\---------------- Loop End ----------------

End

Return

End

SET QUOTED\_IDENTIFIER OFF

SET ANSI\_NULLS ON

SET QUOTED\_IDENTIFIER OFF

SET ANSI\_NULLS OFF

/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

\* Name: T-SQL Split算法实现（整型）

\* Author:　Sman Huang

\* ALTER Date:

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/

ALTER Function dbo.ufc\_SplitInt

(

@InputStr varchar(250)

)

Returns @ufc\_SplitInt table

(

val int

)

Begin

Declare @str varchar(250)

Declare @substr varchar(250)

Declare @iLen int

Declare @iStart int

set @str=RTrim(Ltrim(@InputStr))

set @iStart=CHARINDEX( ',' , @str )

set @iLen=Len( @str )

IF @iStart>0

Begin

set @substr=substring( @str , 1 , @iStart-1 )

set @str=substring( @str , @iStart+1 , @iLen-@iStart )

End

Else

Begin

set @substr=@str

set @str=''

End

set @substr=RTRIM( LTRIM( @substr ) )

insert @ufc\_SplitInt select id=cast( @substr as int )

While Len( @str )>0

Begin

\---------------- Loop Begin ---------------

set @iStart=CHARINDEX( ',' , @str )

set @iLen=Len( @str )

IF @iStart>0

Begin

set @substr=substring( @str , 1 , @iStart-1 )

set @str=substring( @str , @iStart+1 , @iLen-@iStart )

End

Else

Begin

set @substr=@str

set @str=''

End

set @substr=RTRIM( LTRIM( @substr ) )

insert @ufc\_SplitInt select id=cast( @substr as int )

\---------------- Loop End ----------------

End

Return

End

\--下面这个是一个自定义函数，用户可以调用这个函数判断指定的字符串是否符合正则表达式的规则.（转载）  
  
CREATE FUNCTION dbo.find\_regular\_expression  
(  
@source varchar(5000), --需要匹配的源字符串  
@regexp varchar(1000), --正则表达式  
@ignorecase bit = 0 --是否区分大小写，默认为false  
)  
RETURNS bit --返回结果0-false，1-true  
AS  
BEGIN  
  
\--0（成功）或非零数字（失败），是由 OLE 自动化对象返回的 HRESULT 的整数值。  
DECLARE @hr integer  
  
\--用于保存返回的对象令牌，以便之后对该对象进行操作  
DECLARE @objRegExp integer DECLARE @objMatches integer  
  
\--保存结果  
DECLARE @results bit  
  
/\*  
创建 OLE 对象实例,只有 sysadmin 固定服务器角色的成员才能执行 sp\_OACreate,并确定机器中有VBScript.RegExp类库  
\*/  
EXEC @hr = sp\_OACreate 'VBScript.RegExp', @objRegExp OUTPUT  
IF @hr <> 0 BEGIN  
SET @results = 0  
RETURN @results  
END  
/\*  
以下三个分别是设置新建对象的三个属性。下面是'VBScript.RegExp'中常用的属性举例：  
Dim regEx,Match,Matches '建立变量。  
Set regEx = New RegExp '建立一般表达式。  
regEx.Pattern= patrn '设置模式。  
regEx.IgnoreCase = True '设置是否区分大小写。  
regEx.Global=True '设置全局可用性。  
set Matches=regEx.Execute(string) '重复匹配集合  
RegExpTest = regEx.Execute(strng) '执行搜索。  
for each match in matches '重复匹配集合  
RetStr=RetStr &"Match found at position "  
RetStr=RetStr&Match.FirstIndex&".Match Value is '"  
RetStr=RetStr&Match.Value&"'."&vbCRLF Next  
RegExpTest=RetStr  
  
\*/  
EXEC @hr = sp\_OASetProperty @objRegExp, 'Pattern', @regexp  
IF @hr <> 0 BEGIN  
SET @results = 0  
RETURN @results  
END  
EXEC @hr = sp\_OASetProperty @objRegExp, 'Global', false  
IF @hr <> 0 BEGIN  
SET @results = 0  
RETURN @results  
END  
EXEC @hr = sp\_OASetProperty @objRegExp, 'IgnoreCase', @ignorecase  
IF @hr <> 0 BEGIN  
SET @results = 0  
RETURN @results  
END  
\--调用对象方法  
EXEC @hr = sp\_OAMethod @objRegExp, 'Test', @results OUTPUT, @source  
IF @hr <> 0 BEGIN  
SET @results = 0  
RETURN @results  
END  
\--释放已创建的 OLE 对象  
EXEC @hr = sp\_OADestroy @objRegExp  
IF @hr <> 0 BEGIN  
SET @results = 0  
RETURN @results  
END  
RETURN @results  
END  
  
下面是一个简单的测试sql语句，可以直接在查询分析器中运行。  
DECLARE @intLength AS INTEGER  
DECLARE @vchRegularExpression AS VARCHAR(50)  
DECLARE @vchSourceString as VARCHAR(50)  
DECLARE @vchSourceString2 as VARCHAR(50)  
DECLARE @bitHasNoSpecialCharacters as BIT

\-- 初始化变量  
SET @vchSourceString = 'Test one This is a test!!'  
SET @vchSourceString2 = 'Test two This is a test'

\-- 我们的正则表达式应该类似于  
\-- \[a-zA-Z \]\{\}  
\-- 如： \[a-zA-Z \]\{10\} ... 一个十字符的字符串

\-- 获得字符串长度  
SET @intLength = LEN(@vchSourceString)

\-- 设置完整的正则表达式  
SET @vchRegularExpression = '\[a-zA-Z \]\{' + CAST(@intLength as varchar) + '\}'

\-- 是否有任何特殊字符  
SET @bitHasNoSpecialCharacters = dbo.find\_regular\_expression(@vchSourceString, @vchRegularExpression,0)

PRINT @vchSourceString  
IF @bitHasNoSpecialCharacters = 1 BEGIN  
PRINT 'No special characters.'  
END ELSE BEGIN  
PRINT 'Special characters found.'  
END

PRINT '\*\*\*\*\*\*\*\*\*\*\*\*\*\*'

\-- 获得字符串长度  
SET @intLength = LEN(@vchSourceString2)

\-- 设置完整的正则表达式  
SET @vchRegularExpression = '\[a-zA-Z \]\{' + CAST(@intLength as varchar) + '\}'

\-- 是否有任何特殊字符  
SET @bitHasNoSpecialCharacters = dbo.find\_regular\_expression(@vchSourceString2, @vchRegularExpression,0)

PRINT @vchSourceString2  
IF @bitHasNoSpecialCharacters = 1 BEGIN  
PRINT 'No special characters.'  
END ELSE BEGIN  
PRINT 'Special characters found.'  
END

转载于:https://www.cnblogs.com/pinnasky/archive/2010/10/20/1856612.html