Avatar of PAGANED
PAGANED
Flag for United States of America asked on

SQL String Comparison

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

Microsoft SQL Server

Avatar of undefined
Last Comment
PAGANED

8/22/2022 - Mon
vasto

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
PAGANED

ASKER
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.
vasto

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?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
PAGANED

ASKER
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
.
.
vasto

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 ?)
PAGANED

ASKER
There are 6 different groups with 30 different problem codes which equates to 1080 different possibilities.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PAGANED

ASKER
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

ASKER CERTIFIED SOLUTION
vasto

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
PAGANED

ASKER
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
.
.
PAGANED

ASKER
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 + ','
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
vasto

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
vasto

about the starting comma: can you confirm that you have

SET @NewString=null

in the cursor body
vasto

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PAGANED

ASKER
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!
PAGANED

ASKER
Thank you very much!
vasto

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
PAGANED

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