Click here to Skip to main content
14,148,895 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
I have a table in the particular format

id LocationId
1 1,2,31,4,5
2 31,7
3 5,41
4 1,5
5 2

i am using ' like ' for searching but its working only on some expression for example- like '%1%' , like '%1,2%' , like '%4,5%' but when my expression is like '1,31' or like '2,5' is not working.

Please help for the transcendent result. :(
Posted
Updated 11-Aug-14 0:13am
v2
Comments
Bernhard Hiller 12-Aug-14 3:39am
   
Have you ever heard of NORMALIZATION? No? Read some basic database tutorials!
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Your table doesn't have anything like '1,31' or like '2,5'. Instead it can be like '1%,31%' or like '%2%,5%'.
   
Comments
@p@richit 11-Aug-14 6:20am
   
I am using column 'LocationId' for categories. Is there any other option to find the proper result ?
Mayank Vashishtha 11-Aug-14 6:29am
   
you means to say that you are storing all values as comma separated, which are actually primary keys of some other table named as "Location"? Is it so?
@p@richit 11-Aug-14 6:36am
   
yes i used checkboxList to insert the category id in 'LocationId' which inserted same as i posted.
Mayank Vashishtha 11-Aug-14 7:35am
   
and how are you comparing, is it values in a table or a single value?
@p@richit 11-Aug-14 7:39am
   
values in a table.
Mayank Vashishtha 11-Aug-14 7:41am
   
not clear Rajeev. Can you send me the query which you are using. lemme have a look onto it.
@p@richit 11-Aug-14 7:52am
   
take a look on my main table where i create my search query.
http://3.bp.blogspot.com/-sr1s_M_RcUg/U-it0iodrkI/AAAAAAAACZw/T1dzuDC1oCw/s1600/1111.JPG

and my sql quey is

alter proc Sproc_SelectUsersOfShowcase
@category varchar(100),
@location varchar(100)
as
select PhotographerContributors_tbl.*,tbl_City.City as Cityname from PhotographerContributors_tbl
left outer join tbl_City on tbl_City.CityID=PhotographerContributors_tbl.Location

where PhotographerContributors_tbl.Category like '%'+ @Category+'%' and PhotographerContributors_tbl.Location in (@location)
@p@richit 11-Aug-14 7:56am
   
should result from the table example @category=2,12 and @location=379
Magic Wonder 11-Aug-14 9:01am
   
Hey,

You are storing values as static text. For searching values as per your requirement Like will not work. You have to break the Comma separated values row wise and then search the required output.
@p@richit 11-Aug-14 9:08am
   
@Magic Wonder thanks for reply.
and from where i break commas in sql or in my code.if you have any code/function suggestion for the particular Comma separated row to filer search please suggest.




Magic Wonder 11-Aug-14 9:20am
   
Check edited Solution. You will get the function to split the delimited values.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Hi,

It will not give output, since your data does not contains the values which you are looking.

Since Like works on Patterns. Check this Like[^]




Added...


You can make use of IN[^]


Edited

Check this for Splitting values

How to Split a Column value in Sql Server[^]


Hope this will help you.

Cheers
   
v3
Comments
@p@richit 11-Aug-14 6:22am
   
:'( is there any other option for find the proper result?
Magic Wonder 11-Aug-14 6:52am
   
Check Edited Reply
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

Hi Rajeev ,

select * from test9028396 where locationid like '%[25]%'
   
v2
Comments
@p@richit 12-Aug-14 3:00am
   
Not working
Vishal.Singh879 12-Aug-14 3:05am
   
what result you want
@p@richit 12-Aug-14 3:08am
   
when my parameter is 2,5 then the result will be id= 1,3,4,5
Vishal.Singh879 12-Aug-14 3:20am
   
Just Check this one
select * from test9028396 where locationid like '%[25]%'
Magic Wonder 12-Aug-14 5:57am
   
good but it is considering the single digits for searching. e.g. If you pass 3 then it will return id 1 and 2 but actually 3 in values does not exists.
@p@richit 12-Aug-14 5:59am
   
yes that is what i am saying
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

Hi,

Check this ...

As per your required O/P

--PR_GET_VAL '1,31'
CREATE PROC PR_GET_VAL
(
@PA_SERACH_VAL VARCHAR(200)
)
AS
BEGIN

CREATE TABLE #TMP_SEARCH_VAL
(
COL1 VARCHAR(10)
)

INSERT INTO #TMP_SEARCH_VAL
SELECT * from split(@PA_SERACH_VAL,',')


CREATE TABLE #TMP_MAIN_TBL
(
ID INT,
LOCID VARCHAR(10)
)

DECLARE @CNT INT
DECLARE @MAXCNT INT

SELECT @MAXCNT=MAX(id) FROM test_Main_TBL

SET @CNT=1

DECLARE @LOC_VALS VARCHAR(200)

PRINT(@MAXCNT)
PRINT(@CNT)

WHILE @CNT <= @MAXCNT
BEGIN
SELECT @LOC_VALS=locationId from  test_Main_TBL WHERE id=@CNT

INSERT INTO  #TMP_MAIN_TBL
SELECT @CNT,* FROM SPLIT(@LOC_VALS,',')

PRINT(@CNT)

SET @CNT= @CNT + 1
END 

SELECT DISTINCT ID FROm #TMP_MAIN_TBL WHERE LOCID IN (SELECT COL1 FROM #TMP_SEARCH_VAL)

END


Caution : Execution May get slow as Data increases.


Hope this will help you.


Cheers
   
v2
Comments
@p@richit 14-Aug-14 0:38am
   
great its working fine :)
Magic Wonder 14-Aug-14 1:42am
   
Thnx.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web06 | 2.8.190518.1 | Last Updated 13 Aug 2014
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100