Deconstructing and Reconstructing a character string field.

  • I need to take a character description field and eliminate any charges ($) information that may be contained in the description.

    For example, I need the following description:

    380 Y cases @ $.0684, 942 Z cases @ $.0634, 5 R-Badwood, 750 X cases @ $.0634

    To read like this:

    380 Y cases, 942 Z cases, 5 R-Badwood, 750 X cases

    I'm close to having this worked out, I think. The problem is related to the search character (to be found) in the CHARINDEX parts of my query. I search the string for "@" which is the one constant I can know is going to be present when charge data is in the description.

    The problem is that anytime the string contains "other" data not related to charges, like the "5 R-Badwood" part, then the subsequent charges data repeats like:

    5 R-Badwood, 750 X cases , 750 X cases , 942 Z cases , 380 Y cases

    --------- Load a Sample Description to Table Variable ------

    DECLARE @String varchar(1000)

    SET @String = '380 Y cases @ $.0684, 942 Z cases @ $.0634, 5 R-Badwood, 750 X cases @ $.0634'

    --'380 Y cases @ $.0684,942 Z cases @ $.0634,750 E cases @ $.0634,5 R-Badwood' -- Works Correctly --

    --'380 Y cases @ $.0684,942 Z cases @ $.0634,5 R-Badwood,750 X cases @ $.0634' -- Repeats X cases --

    -- '25 Dblstk Plts,445 Z cases @ $.0634' -- Repeat Z cases --

    DECLARE @Description TABLE (num INT, String VARCHAR(1000))

    INSERT INTO @Description

    --------- use numbers table to eliminate $ amounts from the description (start @ ----------

    SELECT Num,

    SUBSTRING(@String,

    CASE Num

    WHEN 1 THEN 1

    ELSE Num + 1

    END,

    CASE CHARINDEX('@', @String, Num + 1)-- looking for position of @ --

    WHEN 0

    THEN LEN(@String) - Num + 1

    ELSE CHARINDEX('@', @String, Num + 1)

    - Num -

    CASE

    WHEN Num > 1

    THEN 1

    ELSE 0

    END

    END

    ) AS String2

    FROM dbo.Numbers--------------- Use The Numbers Table --------------

    WHERE Num <= LEN(@String)

    AND (SUBSTRING(@String, Num, 1) = ','

    OR Num = 1)

    ----------- Re-build the description ------------

    SELECT STUFF((SELECT DISTINCT ', ' + CAST(String AS VARCHAR(1000))

    FROM @Description

    FOR XML PATH('')),1,1,'')

    Any help or suggestions about how to resolve this problem or do it better are greatly appreciated.

    I hope the sample data provided is adequate. Also, I have not included a numbers table definition hoping that is not necessary. Mine start at 1.

    Thanks.

  • It's most certainly not the best solution but it fixes the problem. Altered slightly my final select...

    .

    .

    .

    ----------- Re-build the description ------------

    SELECT STUFF((SELECT DISTINCT ', ' +

    CAST(CASEWHEN CHARINDEX(',', String,1) =''

    THENString

    ELSEsubstring(STRING, 1, CHARINDEX(',', STRING, 1)-1)

    END ASVARCHAR(1000))

    FROM @Description

    FOR XML PATH('')),1,1,'')

    Again, any suggestions for a better solution are very welcomed.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply