SQL字符串处理：实现Procurement与ww间的连字符规范修正

Hey there! Let's work through this string manipulation problem you're facing. You need to standardize the gap between Procurement and ww in your version names, covering two specific cases, right? Let's first look at why your current attempts aren't working, then walk through a reliable solution.

What's Wrong With Your Current Approaches? #

Let's break down the limitations of your two solutions:

• First Solution (STUFF): You're hardcoding the position 12 to insert the hyphen, but this assumes Procurement ends exactly at position 12 and the next character is where you want to replace. If there's a space between Procurement and ww, this replaces the last character of Procurement (the 't') with a hyphen, resulting in something like Procuremen- ww...—not what you want.
• Second Solution (REPLACE): Your replace targets procurement (lowercase 'p') but your input uses Procurement (uppercase 'P'), so it won't match. Also, this only handles the case where there's a space between the two strings—it doesn't address the scenario where Procurement is directly followed by ww (like Procurementww12'18).

The Correct Solution #

We can create a flexible approach that handles both cases (and even edge cases like multiple spaces between the two strings) by finding the positions of Procurement and ww dynamically, then adjusting the string accordingly.

Option 1: Standalone Logic for a Single String #

Here's how to handle a single @versionname variable:

DECLARE @versionname VARCHAR(100) = 'Procurement ww12''18 abc-as'
DECLARE @procStr VARCHAR(20) = 'Procurement'
DECLARE @wwStr VARCHAR(5) = 'ww'
DECLARE @procLen INT = LEN(@procStr)
DECLARE @wwPos INT = CHARINDEX(@wwStr, @versionname, @procLen + 1)

-- Skip processing if Procurement/ww aren't found, or if hyphen already exists
IF CHARINDEX(@procStr, @versionname) = 0 OR @wwPos = 0
    PRINT @versionname
ELSE IF SUBSTRING(@versionname, @procLen + 1, 1) = '-'
    PRINT @versionname
ELSE
BEGIN
    -- Case 1: There are characters (like spaces) between Procurement and ww
    IF @wwPos > @procLen + 1
        SET @versionname = STUFF(@versionname, @procLen + 1, @wwPos - @procLen - 1, '-')
    -- Case 2: No characters between Procurement and ww
    ELSE IF @wwPos = @procLen + 1
        SET @versionname = STUFF(@versionname, @procLen + 1, 0, '-')
    
    PRINT @versionname
END

Option 2: Reusable Function #

For bulk processing or repeated use, wrap the logic in a scalar function:

CREATE FUNCTION dbo.FixProcurementWW(@versionname VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
    DECLARE @procStr VARCHAR(20) = 'Procurement'
    DECLARE @wwStr VARCHAR(5) = 'ww'
    DECLARE @procLen INT = LEN(@procStr)
    DECLARE @wwPos INT = CHARINDEX(@wwStr, @versionname, @procLen + 1)

    -- Return original string if Procurement/ww are missing, or hyphen already exists
    IF CHARINDEX(@procStr, @versionname) = 0 OR @wwPos = 0
        RETURN @versionname
    IF SUBSTRING(@versionname, @procLen + 1, 1) = '-'
        RETURN @versionname

    -- Handle the two target cases
    IF @wwPos > @procLen + 1
        SET @versionname = STUFF(@versionname, @procLen + 1, @wwPos - @procLen - 1, '-')
    ELSE IF @wwPos = @procLen + 1
        SET @versionname = STUFF(@versionname, @procLen + 1, 0, '-')

    RETURN @versionname
END

Testing the Function #

Run this query to verify it works for all your test cases:

SELECT dbo.FixProcurementWW('Procurement-ww13''18') AS FormattedVersion
UNION ALL
SELECT dbo.FixProcurementWW('Procurement ww13''18')
UNION ALL
SELECT dbo.FixProcurementWW('Procurementww12''18')
UNION ALL
SELECT dbo.FixProcurementWW('Procurement ww12''18 abc-as')
UNION ALL
SELECT dbo.FixProcurementWW('Procurement ww11''18 NON-VMI (copy 5551)')

This will output exactly what you're expecting:

Procurement-ww13'18
Procurement-ww13'18
Procurement-ww12'18
Procurement-ww12'18 abc-as
Procurement-ww11'18 NON-VMI (copy 5551)

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