如何获取SQL子串?

问题描述

我有一列包含如下值:

"AAAA\BBBBBBBB\CCC"(A、B、C部分长度不固定.)

"AAAA\BBBBBBBB\CCC" (A, B, C part length are not fixed.)

如果存在 \CCC 部分，我需要修剪掉它，如果不存在，则不理会它.

I need to trim out the \CCC part if it exists, and leave it alone if not exist.

例如:

AAA\BBBBBB\CCC -> AAA\BBBBBB

AAA\BBBBBB\CCC -> AAA\BBBBBB

AA\BBBB -> AA\BBBB

AA\BBBB -> AA\BBBB

AA -> AA

抱歉我不够清楚，A、B、C 部分不是字面上的 ABC，它们可以是任何内容.

也应该删除 \DDD\EEEE(etc.)

推荐答案

虽然在纯 T-SQL 中肯定有一种方法可以做到这一点，但它可能没有想象的那么清楚.

While there's certainly a way to do it in pure T-SQL, it might not be as clear as it could be.

您可能需要考虑使用 SQLCLR 基于用户定义的函数 (UDF).有了它，您将能够受益于 c#(或 vb.net) 在 Sql Server 中.

You may want to consider using a SQLCLR-based user defined function (UDF) instead. With that you'll be able to benefit from the power and clarity of c# (or vb.net) within Sql Server.

只要让您的函数将您的字符串作为参数接收并返回您想要的输出作为标量值即可.从那时起，您将能够像使用您可能已经拥有的任何其他 UDF 一样使用该功能.这样你的代码会更容易编写(以及之后的阅读/维护).

Just make it so that your function will receive your string as a param and return the output that you want as a scalar value. From then on you'll be able to use that function just like any other UDF you may already have. This way your code would be much easier to write (and read / maintain afterwards).

您的 sqlclr 函数可以很容易写成这样(伪代码):

Your sqlclr function could be as easy to write as this (pseudo code):

public static string SpecialSubstring(string input)
{
    if (input == null) return null;

    var builder = new StringBuilder();
    var occurences = 0;

    for (i = 0; i < input.Length; i++;)
    {
        var current = input[i];

        if (current == '\') occurences += 1;
        if (occurences >= 2) break;

        builder.Append(current)
    }

    return builder.ToString();
}

然后，来自 T-SQL:

Then, from T-SQL:

SELECT
    [owningschema].SpecialSubstring('AAA\BBBBBB\CCC'), -- returns 'AAA\BBBBBB'
    [owningschema].SpecialSubstring('AA\BBBB'),, -- returns 'AA\BBBB'
    [owningschema].SpecialSubstring('AA') -- returns 'AA'

此页面将为您提供入门所需的几乎所有内容:

This page will give you pretty much all you need to get started:

SQL Server 公共语言运行时集成

这篇关于如何获取SQL子串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！