May 18, 2010 at 4:00 pm
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.
May 18, 2010 at 7:56 pm
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