How to search exact match case

Question

How to search exact match case

shagil.a.gopinath

SSC-Addicted

Points: 494
More actions
July 19, 2016 at 11:28 pm

#328312

Hi
In my table component information ID is stored as comma separated whenever multiple items are selected from the interface.
Scenario – 1
I would like to search with ComponentInfoID = 1
Result
I should get the Count = 4
Scenario – 2
I would like to search with ComponentInfoID = 1,16
I should get the Count = 1
Can someone help me out
Table :
CREATE TABLE [dbo].[ComponentInfoID](
[ComponentInfoID] [varchar](150) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
insert into [dbo].[ComponentInfoID]([ComponentInfoID]) values('1,2')
insert into [dbo].[ComponentInfoID]([ComponentInfoID]) values('1,3')
insert into [dbo].[ComponentInfoID]([ComponentInfoID]) values('1,11')
insert into [dbo].[ComponentInfoID]([ComponentInfoID]) values('1,13,16')
insert into [dbo].[ComponentInfoID]([ComponentInfoID]) values('2,3')
insert into [dbo].[ComponentInfoID]([ComponentInfoID]) values('2,5,8,9')
Sample Table is created for demo purpose

Viewing 10 posts - 1 through 9 (of 9 total)

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

pietlinden SSC Guru Points: 63435 More actions · Answer 1

At first I thought using DelimitedSplit8K would work for this... but I guess I didn't read the fine print carefully enough.

That will happen at 1:30 in the morning sometimes!

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 2

There are quite few options here and each has it's pros and cons but to recommend any of those I would like to have more information as what may be suitable for a small number of options may not work for a large number etc.

1) How many distinct options do you expect to have?

2) How many of those will each user select (max)?

3) What is the expected cardinality of the search set (user count?)?

4) Any other relevant information you want to share?

😎

Alan Burstein SSC Guru Points: 61136 More actions · Answer 3

One approach using the DelimitedSplit8K_LEAD[/url]:

DECLARE @searchstring varchar(100) = '2,5';

SELECT searchstring = @searchstring, [count] = COUNT(*)

FROM

(

SELECT c.ComponentInfoID, mx, [count] = COUNT(*)/mx

FROM ComponentInfoID c

CROSS APPLY dbo.DelimitedSplit8K_LEAD(c.ComponentInfoID,',') s

INNER JOIN

(

SELECT item, mx = MAX(ItemNumber) OVER ()

FROM dbo.DelimitedSplit8K_LEAD(@searchstring,',')

) X ON X.Item = s.Item

GROUP BY c.ComponentInfoID, mx

HAVING COUNT(*) >= mx

) XX;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

This should work for you, but your table design is incorrect. In the long term are much better off breaking the various component ID numbers down into separate rows.

D ECLARE @searchstring varchar(100) = '2,5';

WITH cte as (SELECT c.[ComponentInfoID], Item

FROM ComponentInfoID c

CROSS APPLY dbo.DelimitedSplit8K(c.[ComponentInfoID],',') s

)

select ComponentInfoID, count(*)

from cte

where Item in (select item from dbo.DelimitedSplit8K(@searchstring,','))

group by ComponentInfoID

Are you familiar with dbo.DelimitedSplit8k ??

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

The best solution you can have is to normalize your data.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286575 More actions · Answer 6

Luis Cazares (7/20/2016)
The best solution you can have is to normalize your data.

+10000

It is also the only long term maintainable solution.

OP - when you cram multiple values like that into a single tuple you are violating 1NF. What that means to you is untold amounts of anguish trying to do something that should be really simple (like this query). Sure it might be painful to fix it now but the pain involved is nothing like working with this type of structure in the long term.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 7

I have seen many of such solutions fail, OP, do you want to answer the questions I have asked or even better improve the scheme as suggested?

😎

Smendle Hall of Fame Points: 3969 More actions · Answer 8

shagil.a.gopinath (7/19/2016)
Hi
In my table component information ID is stored as comma separated whenever multiple items are selected from the interface.
Scenario – 1
I would like to search with ComponentInfoID = 1
Result
I should get the Count = 4
Scenario – 2
I would like to search with ComponentInfoID = 1,16
I should get the Count = 1
Can someone help me out
Table :
CREATE TABLE [dbo].[ComponentInfoID](
[ComponentInfoID] [varchar](150) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
insert into [dbo].[ComponentInfoID]([ComponentInfoID]) values('1,2')
insert into [dbo].[ComponentInfoID]([ComponentInfoID]) values('1,3')
insert into [dbo].[ComponentInfoID]([ComponentInfoID]) values('1,11')
insert into [dbo].[ComponentInfoID]([ComponentInfoID]) values('1,13,16')
insert into [dbo].[ComponentInfoID]([ComponentInfoID]) values('2,3')
insert into [dbo].[ComponentInfoID]([ComponentInfoID]) values('2,5,8,9')
Sample Table is created for demo purpose

While I loathe to place a solution for what you are asking for the very reasons explained before my post.....Based on your small dataset and given the constraint of NOT repeating the same number in your Comma Separated Values (i.e. 1,1,2,2 will NOT work for exclusive matching)

SELECT COUNT(1) FROM ComponentInfoID t1 where

t1.ComponentInfoId LIKE '%1%' and t1.ComponentInfoID LIKE '%16%'

SELECT COUNT(1) FROM ComponentInfoID t1 where

t1.ComponentInfoId LIKE '%1%'

/ducks

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 9

While I loathe to place a solution for what you are asking for the very reasons explained before my post.....Based on your small dataset and given the constraint of NOT repeating the same number in your Comma Separated Values (i.e. 1,1,2,2 will NOT work for exclusive matching)
SELECT COUNT(1) FROM ComponentInfoID t1 where
t1.ComponentInfoId LIKE '%1%' and t1.ComponentInfoID LIKE '%16%'
SELECT COUNT(1) FROM ComponentInfoID t1 where
t1.ComponentInfoId LIKE '%1%'

Thus, the DelimitedSplit solutions.

One thing we ALL agree on is that the database design is poor and should be normalized. When simple questions require complicated solutions, there is usually a flaw in the design.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills