SQL String Comparison

PAGANED
PAGANED used Ask the Experts™
on
We have a table that has problem control codes. These codes are assigned to different teams such as ACCT, MULT, BUYR, DATA, EXPD, and WHSE. Some of these problem codes are assigned more than once. Examples:

ProblemControlID = 3
ProblemGroupString = ACCT, EXPD

ProblemControlID = 9
ProblemGroupString = DATA,ACCT,EXPD

If we cycle the cursor for all the records and concatenate, we would end up with a ProblemGroupStringCombined = ACCT,EXPD,DATA,ACCT,EXPD

We want this result:
ProblemGroupStringCombined  = ACCT, EXPD, DATA

In other words, we don't want to repeat any duplicate groups
.
Here is our cursor code that we've been trying to play with.
............................
DECLARE @ProblemGroupStringCombined nvarchar(500)
SET @ProblemGroupStringCombined =  ''
DECLARE ProblemGroupStringCursor CURSOR FOR
SELECT ProblemGroupString FROM tblProblemControlTemp WHERE ProjectID = @ProjectID 
OPEN ProblemGroupStringCursor FETCH NEXT FROM ProblemGroupStringCursor INTO @ProblemGroupString
WHILE @@FETCH_STATUS = 0
BEGIN
	IF @ProblemGroupString is not null
	BEGIN
		SET @ProblemGroupStringCombined = case when charindex(isnull(@ProblemGroupString,''), isnull(@ProblemGroupStringCombined,'')) = 0 then isnull(@ProblemGroupStringCombined,'') + isnull(@ProblemGroupStringCombined,'') else isnull(@ProblemGroupStringCombined,'') end
		
	END
FETCH NEXT FROM ProblemGroupStringCursor INTO @ProblemGroupString
END
CLOSE ProblemGroupStringCursor 
DEALLOCATE ProblemGroupStringCursor   
       

Select @ProblemGroupStringCombined = SUBSTRING (@ProblemGroupStringCombined,1,Len(@ProblemGroupStringCombined)-1)

INSERT INTO #tblProblemString VALUES (@ProjectID, @ProblemString, @ProblemGroupStringCombined)

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011

Commented:
add distinct to the line which defines the cursor in this way:
SELECT DISTINCT ProblemGroupString FROM tblProblemControlTemp WHERE ProjectID = @ProjectID

BTW you can avoid using cursor

try this:
DECLARE @ProblemGroupStringCombined nvarchar(500)
SET @ProblemGroupStringCombined =  null

SELECT @ProblemGroupStringCombined = IsNull(@ProblemGroupStringCombined + ',', '') + ProblemGroupString
FROM (SELECT DISTINCT ProblemGroupString FROM tblProblemControlTemp WHERE ProjectID = @ProjectID) t

Author

Commented:
I don't believe that SELECT DISTINCT applies here. The data looks like this:

ID      ProjectID      ProblemControlID      ProblemGroupString
2318683      214010      3      ACCT,EXPD
2318684      214728      3      ACCT
2318685      214731      3      ACCT,WHSE
2318723      249982      3      ACCT,DATA,EXPD
2318724      240119      4      MULT
2318725      244219      4      BUYR,ACCT
2318726      248056      4      BUYR
2318727      249886      4      DATA,ACCT
2318728      249896      4      DATA,ACCT
2318729      249951      4      WHSE

The results look like this:

ProblemControlResultsID      ProjectID      ProblemString      ProblemGroupString
598267      1      15       BUYR
598268      212721      29       EXPD,WHSE
598269      212722      13       BUYR
598270      212725      21       DATA
598271      212727      21       DATA
598272      212729      21       DATA
598273      212738      22       DATA,EXPD
598274      212745      19,24       DATA
598275      212749      29       EXPD,WHSE
598276      212750      20       DATA
598277      212751      22,29       DATA,EXPDEXPD,WHSE
598278      212753      21       DATA
598279      212768      21,22       DATADATA,EXPD
598280      212785      21       DATA
598281      212791      21       DATA
598282      212824      21       DATA
598283      212828      21       DATA
598284      212847      19       DATA
598285      212884      19       DATA
598286      212885      21       DATA
598287      212889      22       DATA,EXPD
598288      212896      22       DATA,EXPD
598289      212900      21       DATA
598290      212924      29       EXPD,WHSE
598291      212933      22       DATA,EXPD
598292      213005      24       DATA
598293      213206      21       DATA
598294      213448      29       EXPD,WHSE
598295      213452      21       DATA
598296      213465      20,29       DATAEXPD,WHSE
598297      213476      21       DATA
598298      213486      19       DATA
598299      213636      12       BUYR
598300      213637      21       DATA
598301      213646      21       DATA
598302      213648      21       DATA
598303      213651      21       DATA
598304      213673      29       EXPD,WHSE
598305      213674      21       DATA
598306      213675      21       DATA
598307      213681      21       DATA
598308      213715      20,22       DATADATA,EXPD
598309      213724      24,29       DATAEXPD,WHSE

I found a typo in our code. The problem line is:

SET @ProblemGroupStringCombined = case when charindex(isnull(@ProblemGroupString,''), isnull(@ProblemGroupStringCombined,'')) = 0
            then isnull(@ProblemGroupStringCombined,'') + isnull(@ProblemGroupString,'')
            else isnull(@ProblemGroupStringCombined,'') end

The previous example set isnull(@ProblemGroupStringCombined,'') + isnull(@ProblemGroupStringCombined,'') .

I changed it to isnull(@ProblemGroupStringCombined,'') + isnull(@ProblemGroupString,'') .

As you can see, we get close to what we want. The problem is when the ProblemGroupStringCombine contains duplicates. The CharIndex doesn't seem to be working properly.
Top Expert 2011

Commented:
are you trying to concatenate or to parse the string ?

if you are trying to concatenate :

Your cursor is declared with this SQL:
SELECT ProblemGroupString FROM tblProblemControlTemp WHERE ProjectID = @ProjectID
it will return multiple 'ACCT' if there are multiple records

if you add DISTINCT 'ACCT' will be returned just one time:

SELECT DISTINCT ProblemGroupString FROM tblProblemControlTemp WHERE ProjectID = @ProjectID

it will always return just the unique values



Did you try the other code?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Could you clarify for me please:

The nvarchar data = ACCT,EXPD
A second row the nvarchar data = DATA,ACCT,EXPD
.
If we ask for DISTINCT, we will get both rows because each row each row is different than the other.

What we want is ONE row with ACCT,EXPD,DATA
.
0.  Grab the nVarchar data from a row
1.  Cycle through that nVarChar into multiple strings, broken by commas
   -   ACCT   Compare to combined string that we are concatenating
   -   If it exists then do nothing, else concatenate
2.  Move to the next part of the nVarChar
   -  EXPD    Compare to combined string that we are concatenating
   -   If it exists then do nothing, else concatenate
3.  Grab the nVarChar row and repeat 1 & 2 until the end of all data rows
.
.
Top Expert 2011

Commented:
Oh I see, The cursor will get whatever is in ProblemGroupString  adn will add it to @ProblemGroupStringCombined, but you actually wnat to parse first the value in ProblemGroupString   and add to @ProblemGroupStringCombined just the components whic doesn't exists.
How many possible values like DATA  ACCT and EXPD do you have ? ( Do you have any other ?)

Author

Commented:
There are 6 different groups with 30 different problem codes which equates to 1080 different possibilities.

Author

Commented:
We are trying to cycle through ProblemGroupString. This is what we came up with, but it doesn't quite work:

IF @ProblemGroupString is not null
	BEGIN
			WHILE (CHARINDEX(',',@ProblemGroupString) >0)
			BEGIN
				  SELECT SUBSTRING(@ProblemGroupString ,1,CHARINDEX(',',@ProblemGroupString)-1)
				  SELECT @ProblemGroupString = STUFF(@ProblemGroupString,1,CHARINDEX(',',@ProblemGroupString),'')
				  
				  SET @ProblemGroupStringCombined = case when charindex(isnull(@ProblemGroupString,''), isnull(@ProblemGroupStringCombined,'')) = 0 
				  then isnull(@ProblemGroupStringCombined,'') + isnull(@ProblemGroupString,'') 
				  else isnull(@ProblemGroupStringCombined,'') end
			END
	END

Open in new window

Top Expert 2011
Commented:
There are 2 ways to do this ( may be there are more  - I know 2):

1. Use a cursor as you are dong this now and add code which is going to parse the value in ProblemGroupString  

You need a function which is going to parse the value:

CREATE FUNCTION [dbo].[fnTextToTable]
  (@Data TEXT)
  RETURNS @Tbl TABLE (ID VARCHAR(8000))
AS
 
  BEGIN
  DECLARE @Pstn BIGINT, @Value VARCHAR(8000),@NewText VARCHAR(8000)
  SELECT @NewText='/',@Pstn=0
 
  WHILE RTRIM(@NewText) <> ''
    BEGIN
    SET @NewText = SUBSTRING(@Data, @Pstn + 1, 7900)
   
    IF DATALENGTH(@NewText) = 7900
      SET @NewText = SUBSTRING(@NewText,1,CHARINDEX(',', @NewText + ',', DATALENGTH(@NewText) - 1)) + ','
    ELSE
      SET @NewText = @NewText + ','
 
    SET @Pstn = @Pstn + DATALENGTH(@NewText)
    WHILE RTRIM(@NewText) <> ''
      BEGIN
      SET @Value = SUBSTRING(@NewText, 1, CHARINDEX(',', @NewText, 1) -1)
      SET @NewText = STUFF(@NewText, 1, DATALENGTH(@Value) + 1, '')
      IF RTRIM(@Value) <> '' INSERT INTO @Tbl(ID)VALUES(@Value)
      END
    END
 
  RETURN
END

This function will get a string like '1,2,3' and will return a table like this
ID
1
2
3



your cursor will look like this:


DECLARE @ProblemGroupStringCombined nvarchar(500)
DECLARE @NewString nvarchar(500)

SET @ProblemGroupStringCombined =  ''
DECLARE ProblemGroupStringCursor CURSOR FOR
SELECT ProblemGroupString FROM tblProblemControlTemp WHERE ProjectID = @ProjectID
OPEN ProblemGroupStringCursor FETCH NEXT FROM ProblemGroupStringCursor INTO @ProblemGroupString
WHILE @@FETCH_STATUS = 0
BEGIN
      IF @ProblemGroupString is not null
      BEGIN
            SET @NewString=null
            SELECT @NewString = IsNull(@NewString + ',', '') + ID FROM dbo.fnTextToTable(@ProblemGroupString) WHERE ',' + @ProblemGroupStringCombined + ','  NOT LIKE '%,' + ID +',%'
            IF ISNULL(@NewString,'')<>''
                  SET @ProblemGroupStringCombined = @ProblemGroupStringCombined + ',' + @NewString
            
      END
FETCH NEXT FROM ProblemGroupStringCursor INTO @ProblemGroupString
END
CLOSE ProblemGroupStringCursor
DEALLOCATE ProblemGroupStringCursor

 
Can you check if this works for you ?


The other way is to parse the whole table and to concatenate the values by ProjectID

Author

Commented:
Wow, very neat function. I probably only understand 80% of it.
.
Here the return of some of the data with your modifications in place.
Notice the leading commas and repeating values:

ProblemControlResultsID      ProjectID      ProblemString      ProblemGroupString
602617      1      15      ,BUYR
602618      212721      29      ,EXPD,WHSE
602619      212722      13      ,BUYR
602620      212725      21      ,DATA
602621      212727      21      ,DATA
602622      212729      21      ,DATA
602623      212738      22      ,DATA,EXPD
602624      212745      19,24      ,DATA,DATA
602625      212749      29      ,EXPD,WHSE
602626      212750      20      ,DATA
602627      212751      22,29      ,DATA,EXPD,EXPD,WHSE
602628      212753      21      ,DATA
602629      212768      21,22      ,DATA,DATA,EXPD
.
.

Author

Commented:
With your help, we seem to have solved the problem.
Where the fnTextToTable.ID value is not in the @ProblemGroupStringCombined then add the value.
I'm still a little vague on the concept, but it seems to work!
We made a change to your code:

SET @NewString=null
        SELECT @NewString = IsNull(@NewString + ',', '') + ID FROM dbo.fnTextToTable(@ProblemGroupString) WHERE CHARINDEX(ID, @ProblemGroupStringCombined) = 0

        IF ISNULL(@NewString,'')<>''
             SET @ProblemGroupStringCombined = @ProblemGroupStringCombined+ @NewString + ','
Top Expert 2011

Commented:
The change (WHERE CHARINDEX(ID, @ProblemGroupStringCombined)) may not work as expected. I don't know what are your codes , but if you have simliar values this logic may not work. for example if you have values EXPDE and EXPD and @ProblemGroupStringCombined current value is EXPDE:

CHARINDEX('EXPD', 'EXPDE')
CHARINDEX('EXPDE' , 'EXPDE')

both will return 1
Top Expert 2011

Commented:
about the starting comma: can you confirm that you have

SET @NewString=null

in the cursor body
Top Expert 2011

Commented:
about repeating values :can you please replace :
SELECT @NewString = IsNull(@NewString + ',', '') + ID FROM dbo.fnTextToTable(@ProblemGroupString) WHERE ',' + @ProblemGroupStringCombined + ','  NOT LIKE '%,' + ID +',%'



with


SELECT @NewString = IsNull(@NewString + ',', '') + ID FROM
(SELECT DISTINCT ID FROM dbo.fnTextToTable(@ProblemGroupString) WHERE ',' + @ProblemGroupStringCombined + ','  NOT LIKE '%,' + ID +',%') t

Author

Commented:
Thank you very much for your help
.
The group names are all unique and there are only six of them
.
We removed the extra commas
.
I hope that you can help me next time too!

Author

Commented:
Thank you very much!
Top Expert 2011

Commented:
you may add commas to the ID just to avoid issues in the future in case a new  value is added
WHERE CHARINDEX(ID, @ProblemGroupStringCombined) = 0


WHERE CHARINDEX(',' + ID + ',', ',' + @ProblemGroupStringCombined + ',' ) = 0
then the comparisson will be :
CHARINDEX(',EXPD,', ',EXPDE,')
CHARINDEX(',EXPDE,' , ',EXPDE,')



Glad, I was able to help.

Author

Commented:
Excellent point with creating a unique string in the function table
.
Thank you!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial