当前位置：首页 > news >正文

SQL2005CLR函数扩展-字符串函数

news 来源：原创 2024/5/6 8:52:55

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

use sqlclr

exec sp_configure 'clr enabled' , '1'

reconfigure ;

exec sp_configure 'show advanced options' , '1' ;

ALTER DATABASE sqlclr SET TRUSTWORTHY On

CREATE ASSEMBLY SqlClr_typeutilty FROM 'E:\sqlclrdata\SQLCLR.dll' WITH PERMISSION_SET = UnSAFE;

然后我们边注册函数边看效果。