How to get SQL substring?

问题:

I have a column containing value like:

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

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

For example:

AAA\BBBBBB\CCC -> AAA\BBBBBB

AA\BBBB -> AA\BBBB

AA -> AA

Sorry I am not clear enough, the A, B, C parts are not literally ABC, they could be any content.

Also \DDD\EEEE(etc.) should be removed as well

回答1:

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

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.

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).

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();
}

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 Common Language Runtime Integration

回答2:

Here is a solution to remove the last part if there are two or more parts (separated by \)

DECLARE @var VARCHAR(32) = 'AAAA\BBBBBBBB\CCC'

SELECT
    LEN(@var) - LEN(REPLACE(@var, '\', '')) -- Number of occurences of the character \

    , CHARINDEX('\', @var) -- Position of the first occurence
    , LEN(@var) - CHARINDEX('\', REVERSE(@var)) -- Position of the last occurence

    , CASE WHEN LEN(@var) - LEN(REPLACE(@var, '\', '')) > 1 AND CHARINDEX('\', @var) !=  LEN(@var) - CHARINDEX('\', REVERSE(@var))
        THEN SUBSTRING(@var, 1, LEN(@var) - CHARINDEX('\', REVERSE(@var)))
        ELSE @var
    END

回答3:

You can use PATINDEX to find out if the field has 3 (or more) parts, and then some string manipulation to chop off the last part:

select case 
       when PATINDEX(field, '%\%\%') > 0 then 
           /* Chop off last part */
           LEFT(field, len(field) - charindex('\', reverse(field)))
       else
           field
       end case as choppedfield
  from my_table

回答4:

This is how I would accomplish this task:

declare @temp varchar(50), @temp2 varchar(15), @temp3 varchar(15)
set @temp = 'aaaa\bbbb\cccc\dddd'

IF (SELECT LEFT(@temp,CHARINDEX('\',@temp, CHARINDEX('\',@temp,0)+1))) != ''
    BEGIN
      SELECT LEFT(LEFT(@temp,CHARINDEX('\',@temp, CHARINDEX('\',@temp,0)+1)),LEN(LEFT(@temp,CHARINDEX('\',@temp, CHARINDEX('\',@temp,0)+1)))-1)
    END
ELSE
    BEGIN
      select @temp
    END

Paste this into a query window and give it a shot with different @temp values. It does a quick check to see if two '\'s exist, and trims it to the character place before the second '\'. If less than two '\'s exist, it will just return the value itself.