TSQL中如何提取方括号间文本并实现动态替换？

Got it, let's tackle this problem step by step. You need to dynamically pull the text inside square brackets from your @value variable, store it in @department, then use that variable to replace the bracketed content with your target string. Here's a reliable, general-purpose solution that works no matter what the bracketed text is (like department, country, etc.):

Step 1: Extract the Text Inside Square Brackets #

First, we'll use CHARINDEX to locate the positions of the opening [ and closing ] brackets, then SUBSTRING to pull out the text between them. This logic adapts automatically if the bracketed text changes.

Step 2: Perform the Replacement #

Once we have the extracted value, we'll use REPLACE to swap out the original bracketed segment with your desired string.

Full T-SQL Code Example #

DECLARE @value as nvarchar(max)
SET @value = '(company.[department] LIKE ''Development'')'; -- Example input, could be (company.[country] LIKE ''USA'') etc.

-- Extract the text inside square brackets
DECLARE @department nvarchar(max)
SET @department = SUBSTRING(
    @value,
    CHARINDEX('[', @value) + 1, -- Start right after the opening [
    CHARINDEX(']', @value) - CHARINDEX('[', @value) - 1 -- Calculate length of the text between brackets
)

-- Execute the replacement
SELECT REPLACE(@value, '[' + @department + ']', '[another_string]') AS UpdatedValue;

How It Works #

• CHARINDEX('[', @value) finds the starting position of the opening bracket. Adding 1 skips the bracket itself so we start at the first character of the target text.
• CHARINDEX(']', @value) - CHARINDEX('[', @value) - 1 calculates the exact length of the text between the brackets (subtracting the positions of the brackets, then minus 1 to exclude the closing bracket).
• Wrapping @department in [ and ] in the REPLACE function ensures we target the exact bracketed segment, not just the standalone text.

Handling Edge Cases #

If there's a chance your @value might not contain square brackets, add a quick check to avoid errors:

DECLARE @value as nvarchar(max)
SET @value = '(company.department LIKE ''Development'')'; -- No brackets here

DECLARE @department nvarchar(max)
IF CHARINDEX('[', @value) > 0 AND CHARINDEX(']', @value) > CHARINDEX('[', @value)
BEGIN
    SET @department = SUBSTRING(
        @value,
        CHARINDEX('[', @value) + 1,
        CHARINDEX(']', @value) - CHARINDEX('[', @value) - 1
    )
    SELECT REPLACE(@value, '[' + @department + ']', '[another_string]') AS UpdatedValue;
END
ELSE
BEGIN
    -- Return original value if no brackets exist
    SELECT @value AS UpdatedValue;
END

This ensures your code doesn't throw errors if the input doesn't match the expected format.

内容的提问来源于stack exchange，提问作者alexithymia