|
|
You're in the
sql server programming
group:Query to find a value in the comma separated field value!!
sql server programming:
I have a table field, having the comma separated values as shown below. Table : Movie MovieId actors[varchar] 1 12,23,44,56 2 26,45,22,32 3 45,22,34,23 I need to query to find a string for Ex: 23 from the actors field. So the expected result is with row 1 & 3 What is the exact simple query statement to do the search?!. Tq. in advance Laks.R
Tq Mr.Itzik Ben-Gan. Thanx a lot
regards Laks.R
Laks, try this:
select movieid from movie where where ','+actors+',' like '%,23,% -- BG, SQL Server MVP www.SolidQualityLearning.com "Lakshmi Narayanan.R" <LakshmiNarayananR@discussions.microsoft.com> wrote in message news:E2BBAD0D-2C0A-4D5D-B4F5-C0D86CC0FE64@microsoft.com... [quoted text, click to view] > Hi experts, > > I have a table field, having the comma separated values as shown below. > > Table : Movie > > MovieId actors[varchar] > 1 12,23,44,56 > 2 26,45,22,32 > 3 45,22,34,23 > > I need to query to find a string for Ex: 23 from the actors field. > > So the expected result is with row 1 & 3 > > > What is the exact simple query statement to do the search?!. > > Tq. in advance > Laks.R >
Is it obvious that I had too much beer before I wrote this? <g>
Correction: select movieid from movie where ','+actors+',' like '%,23,%' -- BG, SQL Server MVP www.SolidQualityLearning.com [quoted text, click to view] "Itzik Ben-Gan" <itzik@REMOVETHIS.SolidQualityLearning.com> wrote in message news:%23bYsPYz3EHA.3336@TK2MSFTNGP11.phx.gbl... > Laks, try this: > > select movieid > from movie > where where ','+actors+',' like '%,23,% > > -- > BG, SQL Server MVP > www.SolidQualityLearning.com > > > "Lakshmi Narayanan.R" <LakshmiNarayananR@discussions.microsoft.com> wrote > in message news:E2BBAD0D-2C0A-4D5D-B4F5-C0D86CC0FE64@microsoft.com... >> Hi experts, >> >> I have a table field, having the comma separated values as shown below. >> >> Table : Movie >> >> MovieId actors[varchar] >> 1 12,23,44,56 >> 2 26,45,22,32 >> 3 45,22,34,23 >> >> I need to query to find a string for Ex: 23 from the actors field. >> >> So the expected result is with row 1 & 3 >> >> >> What is the exact simple query statement to do the search?!. >> >> Tq. in advance >> Laks.R >> > >
This is really a very bad design. Unless this is a very small application
you are going to get horrible performance, not to mention a bad time joining the movie table with the actor table. Every column in your database should contain one and only one value. Rebuilding as: Movie ------ MovieId int primary key, Name varchar(200) unique Actor ------ ActorId int primary key, FirstName varchar(40), MiddleName varchar(40), LastName varchar(40), Suffix varchar(5), Uniquer varchar(40) --note, not exactly sure of the term, but actors usually have numbers that --make their names unique if there are two with the same name. Union thing, I think Unique (FirstName, MiddleName, LastName, Suffix, Uniquer) ActorInMovie --------------- ActorId int (foreign key to actor table) MovieId int (foreign key to movie table) Now you can look for actor 23 using a simple query: select movie.name, actor.firstName, actor.MiddleName, actor.LastName, actor.suffix, actor.uniquer from movie join actorInMovie on movie.movieId = actorInMovie.movieId join actor on actor.actorId = actorInMovie.actorId where actor.actorId = 23 or if you need to find movies with two actors: where actor.actorId in (23, 44) or whatever. It will be much easier to deal with in the long run. -- ---------------------------------------------------------------------------- Louis Davidson - drsql@hotmail.com SQL Server MVP Compass Technology Management - www.compass.net Pro SQL Server 2000 Database Design - http://www.apress.com/book/bookDisplay.html?bID=266 Note: Please reply to the newsgroups only unless you are interested in consulting services. All other replies may be ignored :) "Lakshmi Narayanan.R" <LakshmiNarayananR@discussions.microsoft.com> wrote in message news:E2BBAD0D-2C0A-4D5D-B4F5-C0D86CC0FE64@microsoft.com... [quoted text, click to view] > Hi experts, > > I have a table field, having the comma separated values as shown below. > > Table : Movie > > MovieId actors[varchar] > 1 12,23,44,56 > 2 26,45,22,32 > 3 45,22,34,23 > > I need to query to find a string for Ex: 23 from the actors field. > > So the expected result is with row 1 & 3 > > > What is the exact simple query statement to do the search?!. > > Tq. in advance > Laks.R >
Thank u Mr.Davidson,
Ur suggesstion is really helpful. Our tables are not normalized well. So i have to use like this way for instant. Thank u very much. [quoted text, click to view] "Louis Davidson" wrote:
> This is really a very bad design. Unless this is a very small application > you are going to get horrible performance, not to mention a bad time joining > the movie table with the actor table. Every column in your database should > contain one and only one value. > > Rebuilding as: > > Movie > ------ > MovieId int primary key, > Name varchar(200) unique > > Actor > ------ > ActorId int primary key, > FirstName varchar(40), > MiddleName varchar(40), > LastName varchar(40), > Suffix varchar(5), > Uniquer varchar(40) --note, not exactly sure of the term, but actors usually > have numbers that > --make their names unique if there are two > with the same name. Union thing, I think > Unique (FirstName, MiddleName, LastName, Suffix, Uniquer) > > ActorInMovie > --------------- > ActorId int (foreign key to actor table) > MovieId int (foreign key to movie table) > > Now you can look for actor 23 using a simple query: > > select movie.name, actor.firstName, actor.MiddleName, actor.LastName, > actor.suffix, actor.uniquer > from movie > join actorInMovie > on movie.movieId = actorInMovie.movieId > join actor > on actor.actorId = actorInMovie.actorId > where actor.actorId = 23 > > or if you need to find movies with two actors: > > where actor.actorId in (23, 44) > > or whatever. It will be much easier to deal with in the long run. > > -- > ---------------------------------------------------------------------------- > Louis Davidson - drsql@hotmail.com > SQL Server MVP > > Compass Technology Management - www.compass.net > Pro SQL Server 2000 Database Design - > http://www.apress.com/book/bookDisplay.html?bID=266 > Note: Please reply to the newsgroups only unless you are interested in > consulting services. All other replies may be ignored :) > > "Lakshmi Narayanan.R" <LakshmiNarayananR@discussions.microsoft.com> wrote in > message news:E2BBAD0D-2C0A-4D5D-B4F5-C0D86CC0FE64@microsoft.com... > > Hi experts, > > > > I have a table field, having the comma separated values as shown below. > > > > Table : Movie > > > > MovieId actors[varchar] > > 1 12,23,44,56 > > 2 26,45,22,32 > > 3 45,22,34,23 > > > > I need to query to find a string for Ex: 23 from the actors field. > > > > So the expected result is with row 1 & 3 > > > > > > What is the exact simple query statement to do the search?!. > > > > Tq. in advance > > Laks.R > > > >
I figured, and many many folks are in the same situation you are (and I am
often when dealing with 3rd party systems,) and youi had been given the way to deal with this so I wanted to give you some basic information so in case you ever have to build the same situation it woudn't be like this :) -- ---------------------------------------------------------------------------- Louis Davidson - drsql@hotmail.com SQL Server MVP Compass Technology Management - www.compass.net Pro SQL Server 2000 Database Design - http://www.apress.com/book/bookDisplay.html?bID=266 Note: Please reply to the newsgroups only unless you are interested in consulting services. All other replies may be ignored :) "Lakshmi Narayanan.R" <LakshmiNarayananR@discussions.microsoft.com> wrote in message news:9C505E0D-EA0F-49EC-80CE-A4EAA003BF3B@microsoft.com... [quoted text, click to view] > Thank u Mr.Davidson, > > Ur suggesstion is really helpful. Our tables are not normalized well. So i > have to use like this way for instant. > > Thank u very much. > > "Louis Davidson" wrote: > >> This is really a very bad design. Unless this is a very small >> application >> you are going to get horrible performance, not to mention a bad time >> joining >> the movie table with the actor table. Every column in your database >> should >> contain one and only one value. >> >> Rebuilding as: >> >> Movie >> ------ >> MovieId int primary key, >> Name varchar(200) unique >> >> Actor >> ------ >> ActorId int primary key, >> FirstName varchar(40), >> MiddleName varchar(40), >> LastName varchar(40), >> Suffix varchar(5), >> Uniquer varchar(40) --note, not exactly sure of the term, but actors >> usually >> have numbers that >> --make their names unique if there are >> two >> with the same name. Union thing, I think >> Unique (FirstName, MiddleName, LastName, Suffix, Uniquer) >> >> ActorInMovie >> --------------- >> ActorId int (foreign key to actor table) >> MovieId int (foreign key to movie table) >> >> Now you can look for actor 23 using a simple query: >> >> select movie.name, actor.firstName, actor.MiddleName, actor.LastName, >> actor.suffix, actor.uniquer >> from movie >> join actorInMovie >> on movie.movieId = actorInMovie.movieId >> join actor >> on actor.actorId = actorInMovie.actorId >> where actor.actorId = 23 >> >> or if you need to find movies with two actors: >> >> where actor.actorId in (23, 44) >> >> or whatever. It will be much easier to deal with in the long run. >> >> -- >> ---------------------------------------------------------------------------- >> Louis Davidson - drsql@hotmail.com >> SQL Server MVP >> >> Compass Technology Management - www.compass.net >> Pro SQL Server 2000 Database Design - >> http://www.apress.com/book/bookDisplay.html?bID=266 >> Note: Please reply to the newsgroups only unless you are interested in >> consulting services. All other replies may be ignored :) >> >> "Lakshmi Narayanan.R" <LakshmiNarayananR@discussions.microsoft.com> wrote >> in >> message news:E2BBAD0D-2C0A-4D5D-B4F5-C0D86CC0FE64@microsoft.com... >> > Hi experts, >> > >> > I have a table field, having the comma separated values as shown below. >> > >> > Table : Movie >> > >> > MovieId actors[varchar] >> > 1 12,23,44,56 >> > 2 26,45,22,32 >> > 3 45,22,34,23 >> > >> > I need to query to find a string for Ex: 23 from the actors field. >> > >> > So the expected result is with row 1 & 3 >> > >> > >> > What is the exact simple query statement to do the search?!. >> > >> > Tq. in advance >> > Laks.R >> > >> >> >>
Hi Dimant,
Tq for u message. If v search like using " like '%23%' ", all the id's starting and ending with 23 will be displayed. But i need the exact id what comes between the %% characters. Mr.Itzik Ben-Gan did the exact code. It has satisfied the exact need. Tq. [quoted text, click to view] "Uri Dimant" wrote:
> Hi > CREATE TABLE #Test > ( > col INT, > col1 VARCHAR(100) > ) > INSERT INTO #Test VALUES (1,'12,23,44,56') > INSERT INTO #Test VALUES (2,'26,45,22,32') > INSERT INTO #Test VALUES (3,'45,22,34,23') > > select * from #test where col1 like '%23%' > > > > "Lakshmi Narayanan.R" <LakshmiNarayananR@discussions.microsoft.com> wrote in > message news:E2BBAD0D-2C0A-4D5D-B4F5-C0D86CC0FE64@microsoft.com... > > Hi experts, > > > > I have a table field, having the comma separated values as shown below. > > > > Table : Movie > > > > MovieId actors[varchar] > > 1 12,23,44,56 > > 2 26,45,22,32 > > 3 45,22,34,23 > > > > I need to query to find a string for Ex: 23 from the actors field. > > > > So the expected result is with row 1 & 3 > > > > > > What is the exact simple query statement to do the search?!. > > > > Tq. in advance > > Laks.R > > > >
Hi
CREATE TABLE #Test ( col INT, col1 VARCHAR(100) ) INSERT INTO #Test VALUES (1,'12,23,44,56') INSERT INTO #Test VALUES (2,'26,45,22,32') INSERT INTO #Test VALUES (3,'45,22,34,23') select * from #test where col1 like '%23%' "Lakshmi Narayanan.R" <LakshmiNarayananR@discussions.microsoft.com> wrote in message news:E2BBAD0D-2C0A-4D5D-B4F5-C0D86CC0FE64@microsoft.com... [quoted text, click to view] > Hi experts, > > I have a table field, having the comma separated values as shown below. > > Table : Movie > > MovieId actors[varchar] > 1 12,23,44,56 > 2 26,45,22,32 > 3 45,22,34,23 > > I need to query to find a string for Ex: 23 from the actors field. > > So the expected result is with row 1 & 3 > > > What is the exact simple query statement to do the search?!. > > Tq. in advance > Laks.R >
Uri,
col1 like '%23%' is true if if '23' is a substring of #Test.col1, and this will be the case if col1 is '123456789,11', for example. If the user is searching for '23', he or she does not want to find 123456789 instead. Perhaps it was a typo, but the query you wrote below does not have any comma. SK [quoted text, click to view] Uri Dimant wrote:
>Hi >CREATE TABLE #Test >( > col INT, > col1 VARCHAR(100) >) >INSERT INTO #Test VALUES (1,'12,23,44,56') >INSERT INTO #Test VALUES (2,'26,45,22,32') >INSERT INTO #Test VALUES (3,'45,22,34,23') > >--Both queries are provided the same result ,don't they? >select * from #test where col1 like '%23%' >--Itzik's query >select * >from #test >where ','+col1+',' like '%,23,%' > > > > >"Lakshmi Narayanan.R" <LakshmiNarayananR@discussions.microsoft.com> wrote in >message news:D73F35CF-4471-4456-97FE-4ECB4677EF9F@microsoft.com... > > >>Hi Dimant, >>Tq for u message. >>If v search like using " like '%23%' ", all the id's starting and ending >>with 23 will be displayed. But i need the exact id what comes between the >> >> >%% > > >>characters. Mr.Itzik Ben-Gan did the exact code. It has satisfied the >> >> >exact > > >>need. Tq. >> >>"Uri Dimant" wrote: >> >> >> >>>Hi >>>CREATE TABLE #Test >>>( >>> col INT, >>> col1 VARCHAR(100) >>>) >>>INSERT INTO #Test VALUES (1,'12,23,44,56') >>>INSERT INTO #Test VALUES (2,'26,45,22,32') >>>INSERT INTO #Test VALUES (3,'45,22,34,23') >>> >>>select * from #test where col1 like '%23%' >>> >>> >>> >>>"Lakshmi Narayanan.R" <LakshmiNarayananR@discussions.microsoft.com> >>> >>> >wrote in > > >>>message news:E2BBAD0D-2C0A-4D5D-B4F5-C0D86CC0FE64@microsoft.com... >>> >>> >>>>Hi experts, >>>> >>>>I have a table field, having the comma separated values as shown >>>> >>>> >below. > > >>>>Table : Movie >>>> >>>>MovieId actors[varchar] >>>>1 12,23,44,56 >>>>2 26,45,22,32 >>>>3 45,22,34,23 >>>> >>>>I need to query to find a string for Ex: 23 from the actors field. >>>> >>>>So the expected result is with row 1 & 3 >>>> >>>> >>>>What is the exact simple query statement to do the search?!. >>>> >>>>Tq. in advance >>>>Laks.R >>>> >>>> >>>> >>> >>> >>> > > >
Hi
CREATE TABLE #Test ( col INT, col1 VARCHAR(100) ) INSERT INTO #Test VALUES (1,'12,23,44,56') INSERT INTO #Test VALUES (2,'26,45,22,32') INSERT INTO #Test VALUES (3,'45,22,34,23') --Both queries are provided the same result ,don't they? select * from #test where col1 like '%23%' --Itzik's query select * from #test where ','+col1+',' like '%,23,%' "Lakshmi Narayanan.R" <LakshmiNarayananR@discussions.microsoft.com> wrote in message news:D73F35CF-4471-4456-97FE-4ECB4677EF9F@microsoft.com... [quoted text, click to view] > Hi Dimant, > Tq for u message. > If v search like using " like '%23%' ", all the id's starting and ending > with 23 will be displayed. But i need the exact id what comes between the %% > characters. Mr.Itzik Ben-Gan did the exact code. It has satisfied the exact > need. Tq. > > "Uri Dimant" wrote: > > > Hi > > CREATE TABLE #Test > > ( > > col INT, > > col1 VARCHAR(100) > > ) > > INSERT INTO #Test VALUES (1,'12,23,44,56') > > INSERT INTO #Test VALUES (2,'26,45,22,32') > > INSERT INTO #Test VALUES (3,'45,22,34,23') > > > > select * from #test where col1 like '%23%' > > > > > > > > "Lakshmi Narayanan.R" <LakshmiNarayananR@discussions.microsoft.com> wrote in > > message news:E2BBAD0D-2C0A-4D5D-B4F5-C0D86CC0FE64@microsoft.com... > > > Hi experts, > > > > > > I have a table field, having the comma separated values as shown below. > > > > > > Table : Movie > > > > > > MovieId actors[varchar] > > > 1 12,23,44,56 > > > 2 26,45,22,32 > > > 3 45,22,34,23 > > > > > > I need to query to find a string for Ex: 23 from the actors field. > > > > > > So the expected result is with row 1 & 3 > > > > > > > > > What is the exact simple query statement to do the search?!. > > > > > > Tq. in advance > > > Laks.R > > > > > > > > >
Hi Uri, well & nice to hear abt ur willing to get the deep clearance!!
Have u tried the query with all type of input. For Ex: 23,45,56,34 123,45,23,45 45,231,45,45,23 Here, ','+col1+',' like '%,23,%' has a special meaning. ','+col1+',' creates a template to match with the searching value for "first value", "middle value" and if it is a "last value". If u remove the comma concatenation from the field, the first & last value searching for '23' coundnt get match. So that a template like field is created for match with the all. So pls try with the above field values with "ur query" & " Itzik's query". Hope this cleared ur doubt. Tq u all for this create discussion. [quoted text, click to view] "Steve Kass" wrote:
> Uri, > > I think Itzik answered your question, but just in case... Here's what > you originally wrote: > > CREATE TABLE #Test > ( > col INT, > col1 VARCHAR(100) > ) > INSERT INTO #Test VALUES (1,'12,23,44,56') > INSERT INTO #Test VALUES (2,'26,45,22,32') > INSERT INTO #Test VALUES (3,'45,22,34,23') > > --Both queries are provided the same result ,don't they? > select * from #test where col1 like '%23%' > --Itzik's query > select * > from #test > where ','+col1+',' like '%,23,%' > > > For this data, the two queries return the same result. But what if you > want to search for 2 instead of 23? > > -- Wrong result: > select * from #test where col1 like '%2%' > --Itzik's query, correct result: > select * > from #test > where ','+col1+',' like '%,2,%' > > > SK > > Uri Dimant wrote: > > >Hi,Steve > > > >Yes, but if I change my query to to where col1 like '%,23,%' > >so, what does it make different from the Itzik's query? > > > > > >select * > >from #test > >where ','+col1+',' like '%,23,%' > > > >BTW , have you looked at his ddl (OP) ? There were all data with comma > > > >"Steve Kass" <skass@drew.edu> wrote in message > >news:u76V$qS4EHA.1564@TK2MSFTNGP09.phx.gbl... > > > > > >>Uri, > >> > >> col1 like '%23%' > >> > >>is true if if '23' is a substring of #Test.col1, and this will be the > >>case if > >>col1 is '123456789,11', for example. If the user is searching for '23', > >>he or she does not want to find 123456789 instead. > >> > >>Perhaps it was a typo, but the query you wrote below does not have > >>any comma. > >>SK > >> > >>Uri Dimant wrote: > >> > >> > >> > >>>Hi > >>>CREATE TABLE #Test > >>>( > >>>col INT, > >>>col1 VARCHAR(100) > >>>) > >>>INSERT INTO #Test VALUES (1,'12,23,44,56') > >>>INSERT INTO #Test VALUES (2,'26,45,22,32') > >>>INSERT INTO #Test VALUES (3,'45,22,34,23') > >>> > >>>--Both queries are provided the same result ,don't they? > >>>select * from #test where col1 like '%23%' > >>>--Itzik's query > >>>select * > >>> > >>> > >>>from #test > >> > >> > >>>where ','+col1+',' like '%,23,%' > >>> > >>> > >>> > >>> > >>>"Lakshmi Narayanan.R" <LakshmiNarayananR@discussions.microsoft.com> wrote > >>> > >>> > >in > > > > > >>>message news:D73F35CF-4471-4456-97FE-4ECB4677EF9F@microsoft.com... > >>> > >>> > >>> > >>> > >>>>Hi Dimant, > >>>>Tq for u message. > >>>>If v search like using " like '%23%' ", all the id's starting and > >>>> > >>>> > >ending > > > > > >>>>with 23 will be displayed. But i need the exact id what comes between > >>>> > >>>> > >the > > > > > >>>> > >>>> > >>>%% > >>> > >>> > >>> > >>> > >>>>characters. Mr.Itzik Ben-Gan did the exact code. It has satisfied the > >>>> > >>>> > >>>> > >>>> > >>>exact > >>> > >>> > >>> > >>> > >>>>need. Tq. > >>>> > >>>>"Uri Dimant" wrote: > >>>> > >>>> > >>>> > >>>> > >>>> > >>>>>Hi > >>>>>CREATE TABLE #Test > >>>>>( > >>>>>col INT, > >>>>>col1 VARCHAR(100) > >>>>>) > >>>>>INSERT INTO #Test VALUES (1,'12,23,44,56') > >>>>>INSERT INTO #Test VALUES (2,'26,45,22,32') > >>>>>INSERT INTO #Test VALUES (3,'45,22,34,23') > >>>>> > >>>>>select * from #test where col1 like '%23%' > >>>>> > >>>>> > >>>>> > >>>>>"Lakshmi Narayanan.R" <LakshmiNarayananR@discussions.microsoft.com> > >>>>> > >>>>> > >>>>> > >>>>> > >>>wrote in > >>> > >>> > >>> > >>> > >>>>>message news:E2BBAD0D-2C0A-4D5D-B4F5-C0D86CC0FE64@microsoft.com... > >>>>> > >>>>> > >>>>> > >>>>> > >>>>>>Hi experts, > >>>>>> > >>>>>>I have a table field, having the comma separated values as shown > >>>>>> > >>>>>> > >>>>>> > >>>>>> > >>>below. > >>> > >>> > >>> > >>> > >>>>>>Table : Movie > >>>>>> > >>>>>>MovieId actors[varchar] > >>>>>>1 12,23,44,56 > >>>>>>2 26,45,22,32 > >>>>>>3 45,22,34,23 > >>>>>> > >>>>>>I need to query to find a string for Ex: 23 from the actors field. > >>>>>> > >>>>>>So the expected result is with row 1 & 3 > >>>>>> > >>>>>> > >>>>>>What is the exact simple query statement to do the search?!. > >>>>>> > >>>>>>Tq. in advance > >>>>>>Laks.R > >>>>>> > >>>>>> > >>>>>> > >>>>>> > >>>>>> > >>>>> > >>>>> > >>>>> > >>> > >>> > >>> > >>> > > > > > > > >
Hi Uri,
Try with the following sample data: INSERT INTO #Test VALUES (1,'1,23,2') INSERT INTO #Test VALUES (2,'123,1') INSERT INTO #Test VALUES (3,'1234') INSERT INTO #Test VALUES (4,'1,12345') -- BG, SQL Server MVP www.SolidQualityLearning.com [quoted text, click to view] "Uri Dimant" <urid@iscar.co.il> wrote in message news:%23OXqUVP4EHA.2180@TK2MSFTNGP12.phx.gbl... > Hi > CREATE TABLE #Test > ( > col INT, > col1 VARCHAR(100) > ) > INSERT INTO #Test VALUES (1,'12,23,44,56') > INSERT INTO #Test VALUES (2,'26,45,22,32') > INSERT INTO #Test VALUES (3,'45,22,34,23') > > --Both queries are provided the same result ,don't they? > select * from #test where col1 like '%23%' > --Itzik's query > select * > from #test > where ','+col1+',' like '%,23,%' > > > > > "Lakshmi Narayanan.R" <LakshmiNarayananR@discussions.microsoft.com> wrote > in > message news:D73F35CF-4471-4456-97FE-4ECB4677EF9F@microsoft.com... >> Hi Dimant, >> Tq for u message. >> If v search like using " like '%23%' ", all the id's starting and ending >> with 23 will be displayed. But i need the exact id what comes between the > %% >> characters. Mr.Itzik Ben-Gan did the exact code. It has satisfied the > exact >> need. Tq. >> >> "Uri Dimant" wrote: >> >> > Hi >> > CREATE TABLE #Test >> > ( >> > col INT, >> > col1 VARCHAR(100) >> > ) >> > INSERT INTO #Test VALUES (1,'12,23,44,56') >> > INSERT INTO #Test VALUES (2,'26,45,22,32') >> > INSERT INTO #Test VALUES (3,'45,22,34,23') >> > >> > select * from #test where col1 like '%23%' >> > >> > >> > >> > "Lakshmi Narayanan.R" <LakshmiNarayananR@discussions.microsoft.com> > wrote in >> > message news:E2BBAD0D-2C0A-4D5D-B4F5-C0D86CC0FE64@microsoft.com... >> > > Hi experts, >> > > >> > > I have a table field, having the comma separated values as shown > below. >> > > >> > > Table : Movie >> > > >> > > MovieId actors[varchar] >> > > 1 12,23,44,56 >> > > 2 26,45,22,32 >> > > 3 45,22,34,23 >> > > >> > > I need to query to find a string for Ex: 23 from the actors field. >> > > >> > > So the expected result is with row 1 & 3 >> > > >> > > >> > > What is the exact simple query statement to do the search?!. >> > > >> > > Tq. in advance >> > > Laks.R >> > > >> > >> > >> > > >
Hi,Itzik
How are your doing? I've seen the differnece, but look , if I simple change my query to where col1 like '%,23,%' so it will return the same result, can you explain me what makes your query different from main? -------------- select * from #test where ','+col1+',' like '%,23,%' [quoted text, click to view] "Itzik Ben-Gan" <itzik@REMOVETHIS.SolidQualityLearning.com> wrote in message news:O%231$YVS4EHA.2012@TK2MSFTNGP15.phx.gbl... > Hi Uri, > > Try with the following sample data: > > INSERT INTO #Test VALUES (1,'1,23,2') > INSERT INTO #Test VALUES (2,'123,1') > INSERT INTO #Test VALUES (3,'1234') > INSERT INTO #Test VALUES (4,'1,12345') > > -- > BG, SQL Server MVP > www.SolidQualityLearning.com > > > "Uri Dimant" <urid@iscar.co.il> wrote in message > news:%23OXqUVP4EHA.2180@TK2MSFTNGP12.phx.gbl... > > Hi > > CREATE TABLE #Test > > ( > > col INT, > > col1 VARCHAR(100) > > ) > > INSERT INTO #Test VALUES (1,'12,23,44,56') > > INSERT INTO #Test VALUES (2,'26,45,22,32') > > INSERT INTO #Test VALUES (3,'45,22,34,23') > > > > --Both queries are provided the same result ,don't they? > > select * from #test where col1 like '%23%' > > --Itzik's query > > select * > > from #test > > where ','+col1+',' like '%,23,%' > > > > > > > > > > "Lakshmi Narayanan.R" <LakshmiNarayananR@discussions.microsoft.com> wrote > > in > > message news:D73F35CF-4471-4456-97FE-4ECB4677EF9F@microsoft.com... > >> Hi Dimant, > >> Tq for u message. > >> If v search like using " like '%23%' ", all the id's starting and ending > >> with 23 will be displayed. But i need the exact id what comes between the > > %% > >> characters. Mr.Itzik Ben-Gan did the exact code. It has satisfied the > > exact > >> need. Tq. > >> > >> "Uri Dimant" wrote: > >> > >> > Hi > >> > CREATE TABLE #Test > >> > ( > >> > col INT, > >> > col1 VARCHAR(100) > >> > ) > >> > INSERT INTO #Test VALUES (1,'12,23,44,56') > >> > INSERT INTO #Test VALUES (2,'26,45,22,32') > >> > INSERT INTO #Test VALUES (3,'45,22,34,23') > >> > > >> > select * from #test where col1 like '%23%' > >> > > >> > > >> > > >> > "Lakshmi Narayanan.R" <LakshmiNarayananR@discussions.microsoft.com> > > wrote in > >> > message news:E2BBAD0D-2C0A-4D5D-B4F5-C0D86CC0FE64@microsoft.com... > >> > > Hi experts, > >> > > > >> > > I have a table field, having the comma separated values as shown > > below. > >> > > > >> > > Table : Movie > >> > > > >> > > MovieId actors[varchar] > >> > > 1 12,23,44,56 > >> > > 2 26,45,22,32 > >> > > 3 45,22,34,23 > >> > > > >> > > I need to query to find a string for Ex: 23 from the actors field. > >> > > > >> > > So the expected result is with row 1 & 3 > >> > > > >> > > > >> > > What is the exact simple query statement to do the search?!. > >> > > > >> > > Tq. in advance > >> > > Laks.R > >> > > > >> > > >> > > >> > > > > > > >
Uri,
I think Itzik answered your question, but just in case... Here's what [quoted text, click to view] you originally wrote: CREATE TABLE #Test ( col INT, col1 VARCHAR(100) ) INSERT INTO #Test VALUES (1,'12,23,44,56') INSERT INTO #Test VALUES (2,'26,45,22,32') INSERT INTO #Test VALUES (3,'45,22,34,23') --Both queries are provided the same result ,don't they? select * from #test where col1 like '%23%' --Itzik's query select * from #test where ','+col1+',' like '%,23,%' For this data, the two queries return the same result. But what if you want to search for 2 instead of 23? -- Wrong result: select * from #test where col1 like '%2%' --Itzik's query, correct result: select * from #test where ','+col1+',' like '%,2,%' SK [quoted text, click to view] Uri Dimant wrote:
>Hi,Steve > >Yes, but if I change my query to to where col1 like '%,23,%' >so, what does it make different from the Itzik's query? > > >select * >from #test >where ','+col1+',' like '%,23,%' > >BTW , have you looked at his ddl (OP) ? There were all data with comma > >"Steve Kass" <skass@drew.edu> wrote in message >news:u76V$qS4EHA.1564@TK2MSFTNGP09.phx.gbl... > > >>Uri, >> >> col1 like '%23%' >> >>is true if if '23' is a substring of #Test.col1, and this will be the >>case if >>col1 is '123456789,11', for example. If the user is searching for '23', >>he or she does not want to find 123456789 instead. >> >>Perhaps it was a typo, but the query you wrote below does not have >>any comma. >>SK >> >>Uri Dimant wrote: >> >> >> >>>Hi >>>CREATE TABLE #Test >>>( >>>col INT, >>>col1 VARCHAR(100) >>>) >>>INSERT INTO #Test VALUES (1,'12,23,44,56') >>>INSERT INTO #Test VALUES (2,'26,45,22,32') >>>INSERT INTO #Test VALUES (3,'45,22,34,23') >>> >>>--Both queries are provided the same result ,don't they? >>>select * from #test where col1 like '%23%' >>>--Itzik's query >>>select * >>> >>> >>>from #test >> >> >>>where ','+col1+',' like '%,23,%' >>> >>> >>> >>> >>>"Lakshmi Narayanan.R" <LakshmiNarayananR@discussions.microsoft.com> wrote >>> >>> >in > > >>>message news:D73F35CF-4471-4456-97FE-4ECB4677EF9F@microsoft.com... >>> >>> >>> >>> >>>>Hi Dimant, >>>>Tq for u message. >>>>If v search like using " like '%23%' ", all the id's starting and >>>> >>>> >ending > > >>>>with 23 will be displayed. But i need the exact id what comes between >>>> >>>> >the > > >>>> >>>> >>>%% >>> >>> >>> >>> >>>>characters. Mr.Itzik Ben-Gan did the exact code. It has satisfied the >>>> >>>> >>>> >>>> >>>exact >>> >>> >>> >>> >>>>need. Tq. >>>> >>>>"Uri Dimant" wrote: >>>> >>>> >>>> >>>> >>>> >>>>>Hi >>>>>CREATE TABLE #Test >>>>>( >>>>>col INT, >>>>>col1 VARCHAR(100) >>>>>) >>>>>INSERT INTO #Test VALUES (1,'12,23,44,56') >>>>>INSERT INTO #Test VALUES (2,'26,45,22,32') >>>>>INSERT INTO #Test VALUES (3,'45,22,34,23') >>>>> >>>>>select * from #test where col1 like '%23%' >>>>> >>>>> >>>>> >>>>>"Lakshmi Narayanan.R" <LakshmiNarayananR@discussions.microsoft.com> >>>>> >>>>> >>>>> >>>>> >>>wrote in >>> >>> >>> >>> >>>>>message news:E2BBAD0D-2C0A-4D5D-B4F5-C0D86CC0FE64@microsoft.com... >>>>> >>>>> >>>>> >>>>> >>>>>>Hi experts, >>>>>> >>>>>>I have a table field, having the comma separated values as shown >>>>>> >>>>>> >>>>>> >>>>>> >>>below. >>> >>> >>> >>> >>>>>>Table : Movie >>>>>> >>>>>>MovieId actors[varchar] >>>>>>1 12,23,44,56 >>>>>>2 26,45,22,32 >>>>>>3 45,22,34,23 >>>>>> >>>>>>I need to query to find a string for Ex: 23 from the actors field. >>>>>> >>>>>>So the expected result is with row 1 & 3 >>>>>> >>>>>> >>>>>>What is the exact simple query statement to do the search?!. >>>>>> >>>>>>Tq. in advance >>>>>>Laks.R >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>>> >>> >>> >>> >>> > > >
Hi,Steve
Yes, but if I change my query to to where col1 like '%,23,%' so, what does it make different from the Itzik's query? select * from #test where ','+col1+',' like '%,23,%' BTW , have you looked at his ddl (OP) ? There were all data with comma [quoted text, click to view] "Steve Kass" <skass@drew.edu> wrote in message news:u76V$qS4EHA.1564@TK2MSFTNGP09.phx.gbl... > Uri, > > col1 like '%23%' > > is true if if '23' is a substring of #Test.col1, and this will be the > case if > col1 is '123456789,11', for example. If the user is searching for '23', > he or she does not want to find 123456789 instead. > > Perhaps it was a typo, but the query you wrote below does not have > any comma. > SK > > Uri Dimant wrote: > > >Hi > >CREATE TABLE #Test > >( > > col INT, > > col1 VARCHAR(100) > >) > >INSERT INTO #Test VALUES (1,'12,23,44,56') > >INSERT INTO #Test VALUES (2,'26,45,22,32') > >INSERT INTO #Test VALUES (3,'45,22,34,23') > > > >--Both queries are provided the same result ,don't they? > >select * from #test where col1 like '%23%' > >--Itzik's query > >select * > >from #test > >where ','+col1+',' like '%,23,%' > > > > > > > > > >"Lakshmi Narayanan.R" <LakshmiNarayananR@discussions.microsoft.com> wrote in > >message news:D73F35CF-4471-4456-97FE-4ECB4677EF9F@microsoft.com... > > > > > >>Hi Dimant, > >>Tq for u message. > >>If v search like using " like '%23%' ", all the id's starting and ending > >>with 23 will be displayed. But i need the exact id what comes between the > >> > >> > >%% > > > > > >>characters. Mr.Itzik Ben-Gan did the exact code. It has satisfied the > >> > >> > >exact > > > > > >>need. Tq. > >> > >>"Uri Dimant" wrote: > >> > >> > >> > >>>Hi > >>>CREATE TABLE #Test > >>>( > >>> col INT, > >>> col1 VARCHAR(100) > >>>) > >>>INSERT INTO #Test VALUES (1,'12,23,44,56') > >>>INSERT INTO #Test VALUES (2,'26,45,22,32') > >>>INSERT INTO #Test VALUES (3,'45,22,34,23') > >>> > >>>select * from #test where col1 like '%23%' > >>> > >>> > >>> > >>>"Lakshmi Narayanan.R" <LakshmiNarayananR@discussions.microsoft.com> > >>> > >>> > >wrote in > > > > > >>>message news:E2BBAD0D-2C0A-4D5D-B4F5-C0D86CC0FE64@microsoft.com... > >>> > >>> > >>>>Hi experts, > >>>> > >>>>I have a table field, having the comma separated values as shown > >>>> > >>>> > >below. > > > > > >>>>Table : Movie > >>>> > >>>>MovieId actors[varchar] > >>>>1 12,23,44,56 > >>>>2 26,45,22,32 > >>>>3 45,22,34,23 > >>>> > >>>>I need to query to find a string for Ex: 23 from the actors field. > >>>> > >>>>So the expected result is with row 1 & 3 > >>>> > >>>> > >>>>What is the exact simple query statement to do the search?!. > >>>> > >>>>Tq. in advance > >>>>Laks.R > >>>> > >>>> > >>>> > >>> > >>> > >>> > > > > > > > >
Hi Uri, I'm well.
Sure; I think that an example might help: Say col1 = '9239' and you're looking for '23' (filter should be FALSE). You're query said: '9239' LIKE '%23%' returning TRUE incorrectly. My query said: ',9239,' LIKE '%,23,%' returning FALSE correctly. -- BG, SQL Server MVP www.SolidQualityLearning.com [quoted text, click to view] "Uri Dimant" <urid@iscar.co.il> wrote in message news:egulahS4EHA.2600@TK2MSFTNGP09.phx.gbl... > Hi,Itzik > How are your doing? > I've seen the differnece, but look , if I simple change > my query to where col1 like '%,23,%' so it will return the same result, > can > you explain me what makes your query different from main? > -------------- > select * > from #test > where ','+col1+',' like '%,23,%' > > > > "Itzik Ben-Gan" <itzik@REMOVETHIS.SolidQualityLearning.com> wrote in > message > news:O%231$YVS4EHA.2012@TK2MSFTNGP15.phx.gbl... >> Hi Uri, >> >> Try with the following sample data: >> >> INSERT INTO #Test VALUES (1,'1,23,2') >> INSERT INTO #Test VALUES (2,'123,1') >> INSERT INTO #Test VALUES (3,'1234') >> INSERT INTO #Test VALUES (4,'1,12345') >> >> -- >> BG, SQL Server MVP >> www.SolidQualityLearning.com >> >> >> "Uri Dimant" <urid@iscar.co.il> wrote in message >> news:%23OXqUVP4EHA.2180@TK2MSFTNGP12.phx.gbl... >> > Hi >> > CREATE TABLE #Test >> > ( >> > col INT, >> > col1 VARCHAR(100) >> > ) >> > INSERT INTO #Test VALUES (1,'12,23,44,56') >> > INSERT INTO #Test VALUES (2,'26,45,22,32') >> > INSERT INTO #Test VALUES (3,'45,22,34,23') >> > >> > --Both queries are provided the same result ,don't they? >> > select * from #test where col1 like '%23%' >> > --Itzik's query >> > select * >> > from #test >> > where ','+col1+',' like '%,23,%' >> > >> > >> > >> > >> > "Lakshmi Narayanan.R" <LakshmiNarayananR@discussions.microsoft.com> > wrote >> > in >> > message news:D73F35CF-4471-4456-97FE-4ECB4677EF9F@microsoft.com... >> >> Hi Dimant, >> >> Tq for u message. >> >> If v search like using " like '%23%' ", all the id's starting and > ending >> >> with 23 will be displayed. But i need the exact id what comes between > the >> > %% >> >> characters. Mr.Itzik Ben-Gan did the exact code. It has satisfied the >> > exact >> >> need. Tq. >> >> >> >> "Uri Dimant" wrote: >> >> >> >> > Hi >> >> > CREATE TABLE #Test >> >> > ( >> >> > col INT, >> >> > col1 VARCHAR(100) >> >> > ) >> >> > INSERT INTO #Test VALUES (1,'12,23,44,56') >> >> > INSERT INTO #Test VALUES (2,'26,45,22,32') >> >> > INSERT INTO #Test VALUES (3,'45,22,34,23') >> >> > >> >> > select * from #test where col1 like '%23%' >> >> > >> >> > >> >> > >> >> > "Lakshmi Narayanan.R" <LakshmiNarayananR@discussions.microsoft.com> >> > wrote in >> >> > message news:E2BBAD0D-2C0A-4D5D-B4F5-C0D86CC0FE64@microsoft.com... >> >> > > Hi experts, >> >> > > >> >> > > I have a table field, having the comma separated values as shown >> > below. >> >> > > >> >> > > Table : Movie >> >> > > >> >> > > MovieId actors[varchar] >> >> > > 1 12,23,44,56 >> >> > > 2 26,45,22,32 >> >> > > 3 45,22,34,23 >> >> > > >> >> > > I need to query to find a string for Ex: 23 from the actors field. >> >> > > >> >> > > So the expected result is with row 1 & 3 >> >> > > >> >> > > >> >> > > What is the exact simple query statement to do the search?!. >> >> > > >> >> > > Tq. in advance >> >> > > Laks.R >> >> > > >> >> > >> >> > >> >> > >> > >> > >> >> > >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |