sql语句的字符串函数远远没有开发语言的强大，我们可以通过clr来扩展。怎么使用clr写自定义函数我这里就不讲了，不清楚的可以看我之前的blog。下面这个类库可以大大的增强sql语言的函数库，比如字符串的查找更改，时间和数字的格式化等。因为都是一些.Net的基本函数，注释我就不写了，不清楚直接看里面调用的c#函数的注释就可以了^&^
using System;
using System.Data.SqlTypes;
public partial class TypeUtitlty
{
public static int CharIndexOfStartIndex(SqlString child, SqlString parent, SqlInt32 startIndex)
{
if (child.IsNull || parent.IsNull || startIndex.IsNull) return -1;
return parent.Value.IndexOf(child.Value, startIndex.Value);
}
public static int CharLastIndexOf(SqlString child, SqlString parent)
{
if (child.IsNull || parent.IsNull) return -1;
return parent.Value.LastIndexOf(child.Value);
}
public static int CharLastIndexOfStartIndex(SqlString child, SqlString parent, SqlInt32 startIndex)
{
if (child.IsNull || parent.IsNull || startIndex.IsNull) return -1;
return parent.Value.LastIndexOf(child.Value, startIndex.Value);
}
public static SqlString CharInsert(SqlString child, SqlString parent, SqlInt32 startIndex)
{
if (child.IsNull || parent.IsNull || startIndex.IsNull) return SqlString .Null;
return parent.Value.Insert(startIndex.Value,child.Value );
}
public static SqlString CharPadLeft(SqlString parent, SqlString paddingChar, SqlInt32 totalWidth)
{
if (paddingChar.IsNull || parent.IsNull || totalWidth.IsNull || paddingChar.Value.Length==0) return SqlString .Null;
return parent.Value.PadLeft(totalWidth.Value, paddingChar.Value[0]);
}
public static SqlString CharPadRight(SqlString parent, SqlString paddingChar, SqlInt32 totalWidth)
{
if (paddingChar.IsNull || parent.IsNull || totalWidth.IsNull || paddingChar.Value.Length == 0) return SqlString .Null;
return parent.Value.PadRight(totalWidth.Value, paddingChar.Value[0]);
}
public static SqlString CharRemove(SqlString parent, SqlInt32 startIndex, SqlInt32 count)
{
if (parent.IsNull || startIndex.IsNull || count.IsNull) return SqlString .Null;
return parent.Value.Remove(startIndex.Value, count.Value);
}
public static SqlString CharTrim(SqlString parent,SqlString trimChars)
{
if (parent.IsNull || trimChars.IsNull) return SqlString .Null;
return parent.Value.Trim(trimChars.Value.ToCharArray());
}
public static SqlString CharTrimStart(SqlString parent, SqlString trimChars)
{
if (parent.IsNull || trimChars.IsNull) return SqlString .Null;
return parent.Value.TrimStart(trimChars.Value.ToCharArray());
}
public static SqlString CharTrimEnd(SqlString parent, SqlString trimChars)
{
if (parent.IsNull || trimChars.IsNull) return SqlString .Null;
return parent.Value.TrimEnd(trimChars.Value.ToCharArray());
}
public static SqlString DateTimeFormat(SqlDateTime input, SqlString formatting)
{
if (input.IsNull || formatting.IsNull) return SqlString .Null;
return input.Value.ToString(formatting.Value);
}
public static SqlString DoubleFormat(SqlDouble input, SqlString formatting)
{
if (input.IsNull || formatting.IsNull) return SqlString .Null;
return input.Value.ToString(formatting.Value);
}
public static SqlString GUIDFormat(SqlGuid input, SqlString formatting)
{
if (input.IsNull || formatting.IsNull) return SqlString .Null;
return input.Value.ToString(formatting.Value);
}
};
把这个dll编译为SQLCLR.dll，发布到服务器上。
create database sqlclr
go
use sqlclr
go
exec sp_configure 'clr enabled' , '1'
go
reconfigure ;
exec sp_configure 'show advanced options' , '1' ;
go
ALTER DATABASE sqlclr SET TRUSTWORTHY On
go
CREATE ASSEMBLY SqlClr_typeutilty FROM 'E:/sqlclrdata/SQLCLR.dll' WITH PERMISSION_SET = UnSAFE;
--
然后我们边注册函数边看效果。
go
CREATE FUNCTION dbo. ufn_CharIndexOfStartIndex( @child nvarchar ( max ), @parent nvarchar ( max ), @startIndex int )
RETURNS int AS EXTERNAL NAME SqlClr_typeutilty. TypeUtitlty. CharIndexOfStartIndex
-- 获取 @startIndex 之后的首个子串位置
--select dbo.ufn_CharIndexOfStartIndex('jinjazz','jinjazz,jinjazz@csdn.net',2)
-- 【返回8】 , 注意 .net 的字符串起始位置是0不是1
go
CREATE FUNCTION dbo. ufn_CharLastIndexOf( @child nvarchar ( max ), @parent nvarchar ( max ))
RETURNS int AS EXTERNAL NAME SqlClr_typeutilty. TypeUtitlty. CharLastIndexOf
-- 获取末个子串位置
--select dbo.ufn_CharLastIndexOf('z','jinjazz,jinjazz@csdn.net')
-- 【返回14】 , 注意 .net 的字符串起始位置是 0 不是1
go
CREATE FUNCTION dbo. ufn_CharInsert( @child nvarchar ( max ), @parent nvarchar ( max ), @startIndex int )
RETURNS int AS EXTERNAL NAME SqlClr_typeutilty. TypeUtitlty. CharInsert
-- 获取 @startIndex 之前的末个子串位置
--select dbo.ufn_CharInsert('z','jinjazz,jinjazz@csdn.net',10)
-- 【返回6】 , 注意 .net 的字符串起始位置是0不是1
go
CREATE FUNCTION dbo. ufn_CharInsert( @child nvarchar ( max ), @parent nvarchar ( max ), @startIndex int )
RETURNS nvarchar ( max ) AS EXTERNAL NAME SqlClr_typeutilty. TypeUtitlty. CharInsert
--@startIndex 位置插入子串
--select dbo.ufn_CharInsert('jinjazz',' 我爱这个人 ',2)
-- 【返回 : 我爱 jinjazz 这个人】 , 注意 .net 的字符串起始位置是不是
go
CREATE FUNCTION dbo. ufn_CharRemove( @parent nvarchar ( max ), @startIndex int , @count int )
RETURNS nvarchar ( max ) AS EXTERNAL NAME SqlClr_typeutilty. TypeUtitlty. CharRemove
--@startIndex 位置移除子串
--select dbo.ufn_CharRemove(' 我讨厌 jinjazz 这个人 ',3,7)
-- 【返回 : 我讨厌这个人】 , 注意 .net 的字符串起始位置是0不是1
go
CREATE FUNCTION dbo. ufn_CharPadLeft( @input nvarchar ( max ), @paddingChar nvarchar ( max ), @totalWidth int )
RETURNS nvarchar ( max ) AS EXTERNAL NAME SqlClr_typeutilty. TypeUtitlty. CharPadLeft
-- 左补齐字符
--select dbo.ufn_CharPadLeft('jinjazz','-',10)
-- 【返回 :---jinjazz 】
go
CREATE FUNCTION dbo. ufn_CharPadRight( @input nvarchar ( max ), @paddingChar nvarchar ( max ), @totalWidth int )
RETURNS nvarchar ( max ) AS EXTERNAL NAME SqlClr_typeutilty. TypeUtitlty. CharPadRight
-- 右补齐字符
--select dbo.ufn_CharPadRight('jinjazz','-',10)
-- 【返回 :jinjazz--- 】
go
CREATE FUNCTION dbo. ufn_CharTrim( @parent nvarchar ( max ), @trimChars nvarchar ( max ))
RETURNS nvarchar ( max ) AS EXTERNAL NAME SqlClr_typeutilty. TypeUtitlty. CharTrim
-- 首尾截断子串
--select dbo.ufn_CharTrim('( ⊙ o ⊙ )jinjazz{ ⊙ o ⊙ }','(){}o ⊙ ')
-- 【返回 :jinjazz 】
go
CREATE FUNCTION dbo. ufn_CharTrimStart( @parent nvarchar ( max ), @trimChars nvarchar ( max ))
RETURNS nvarchar ( max ) AS EXTERNAL NAME SqlClr_typeutilty. TypeUtitlty. CharTrimStart
-- 首截断子串
--select dbo.ufn_CharTrimStart('( ⊙ o ⊙ )jinjazz{ ⊙ o ⊙ }','(){}o ⊙ ')
-- 【返回 :jinjazz{ ⊙ o ⊙ } 】
go
CREATE FUNCTION dbo. ufn_CharTrimEnd( @parent nvarchar ( max ), @trimChars nvarchar ( max ))
RETURNS nvarchar ( max ) AS EXTERNAL NAME SqlClr_typeutilty. TypeUtitlty. CharTrimEnd
-- 尾截断子串
--select dbo.ufn_CharTrimEnd('( ⊙ o ⊙ )jinjazz{ ⊙ o ⊙ }','(){}o ⊙ ')
-- 【返回 :( ⊙ o ⊙ )jinjazz 】
go
CREATE FUNCTION dbo. ufn_DateTimeFormat( @input datetime , @formatting nvarchar ( 50))
RETURNS nvarchar ( 50) AS EXTERNAL NAME SqlClr_typeutilty. TypeUtitlty. DateTimeFormat
-- 时间格式化
--select dbo.ufn_DateTimeFormat(getdate(),' 现在是 yyyy 年 MM 月 dd 日 hh 点 mm 分 ss 秒 ')
-- 【返回 : 现在是年月日点分秒】
go
CREATE FUNCTION dbo. ufn_DoubleFormat( @input float , @formatting nvarchar ( 50))
RETURNS nvarchar ( 50) AS EXTERNAL NAME SqlClr_typeutilty. TypeUtitlty. DoubleFormat
-- 数字格式化
--select dbo.ufn_DoubleFormat(114534562.876,'c')
-- 【返回 : ￥ ,534,562.88 】
go
CREATE FUNCTION dbo. ufn_GUIDFormat( @input uniqueidentifier , @formatting nvarchar ( 50))
RETURNS nvarchar ( 50) AS EXTERNAL NAME SqlClr_typeutilty. TypeUtitlty. GUIDFormat
-- 数字格式化
--select dbo.ufn_GUIDFormat(newid(),'N')
-- 【返回 :61bf870d334148159ae8ad42d05f2873 】
怎么样，够强大吧。