SQL2005CLR函数扩展-字符串函数

最新推荐文章于 2025-07-12 10:02:44 发布

最新推荐文章于 2025-07-12 10:02:44 发布 · 76 阅读

文章标签：

本文介绍如何通过 SQL CLR 扩展 SQL Server 的字符串处理能力，包括查找替换、格式化等功能，并提供了一个 C# 类库的具体实现。

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;

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