May 21, 2012 at 4:39 am
Hi,
I need to know if a word is avaiable in a comma seperatred text
suppose a value in table1 is 'ram'
i need to know if 'ram' is available in column2 of table2. the values of column2 will be like ram,sham,ravi
---- Drop table #basetable
Create table #basetable(
Idint identity,
Component varchar(256),
)
----Drop table #dailytable
Create table #dailytable(
ID Int,
FILENAMEChar(50),
PROCESSChar(50),
)
insert into #basetable Values( 'ravi,raja ')
insert into #basetable Values( 'raja,jim,sham')
insert into #basetable Values( 'prem,john,pal,ram')
Insert into #dailytable values(1, 'In' ,'Ram ')
Insert into #dailytable values(2, 'out' ,'prem')
Insert into #dailytable values(3, 'In-out ', 'Ravi' )
Select * from #basetable
Select * from #dailytable
May 21, 2012 at 4:47 am
carthik (5/21/2012)
Hi,I need to know if a word is avaiable in a comma seperatred text
suppose a value in table1 is 'ram'
i need to know if 'ram' is available in column2 of table2. the values of column2 will be like ram,sham,ravi
---- Drop table #basetable
Create table #basetable(
Idint identity,
Component varchar(256),
)
----Drop table #dailytable
Create table #dailytable(
ID Int,
FILENAMEChar(50),
PROCESSChar(50),
)
insert into #basetable Values( 'ravi,raja ')
insert into #basetable Values( 'raja,jim,sham')
insert into #basetable Values( 'prem,john,pal,ram')
Insert into #dailytable values(1, 'In' ,'Ram ')
Insert into #dailytable values(2, 'out' ,'prem')
Insert into #dailytable values(3, 'In-out ', 'Ravi' )
Select * from #basetable
Select * from #dailytable
firstly - i would not ever recommend storing comma delimeted data in a single column (see 1st normal form - "remove repeating data!")
second - your question seems to be pretty straightforward, but i'm not sure why you put the #dailytable into your post as it doesn't seem relevant to the question.
could you post your expected results ?????? - maybe i'm just misreading your question
SELECT * frmo #basetable where component like 'ram,%' or component like '%,ram,%' or component like '%,ram'
it may seem needless to do 3 like statements, but if you just do - like '%ram%' then you will get false matches - e.g. "mirama" would also trigger a match
MVDBA
May 21, 2012 at 5:16 am
Are you attempting to join your two tables based on the comma deliminated list in "component" and the "process" ?
If so, something like this might help: -
SELECT *
FROM #dailytable daily
INNER JOIN (SELECT Id, a.b.value('.', 'varchar(100)')
FROM (SELECT Id, CAST(
'<xmlList><element item="'+REPLACE(Component,',',' " /><element item="')
+ '" /></xmlList>' AS XML)
FROM #basetable) data(Id,Component)
CROSS APPLY Component.nodes('xmlList/element/@item') a(b)
) base(Id,Components) ON daily.PROCESS = base.Components
ORDER BY daily.ID;
May 21, 2012 at 1:50 pm
not sure if this will help, but check this out.
http://www.sqlservercentral.com/articles/Tally+Table/72993/
has a great function for splitting comma delimited rows.
Big thanks to Jeff Moden.
Leonard
May 21, 2012 at 8:11 pm
If you're trying to join the two tables based on common names, you can also use PATINDEX.
Create table #basetable(Id int identity, Component varchar(256))
Create table #dailytable(ID Int, FILENAME Char(50), PROCESS Char(50))
insert into #basetable Values( 'ravi,raja ')
insert into #basetable Values( 'raja,jim,sham')
insert into #basetable Values( 'prem,john,pal,ram')
insert into #basetable Values( 'ramindra,john,pal')
Insert into #dailytable values(1, 'In' ,'Ram ')
Insert into #dailytable values(2, 'out' ,'prem')
Insert into #dailytable values(3, 'In-out ', 'Ravi' )
SELECT *
FROM #basetable
INNER JOIN #dailytable
ON PATINDEX('%,' + RTRIM(Process), Component) <> 0 OR
PATINDEX(RTRIM(Process) + ',%', Component) <> 0
DROP TABLE #basetable, #dailytable
You may also have to use the 3rd case described above for LIKE, depending on your data.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 22, 2012 at 1:15 am
It's all going to suck for performance. Don't store comma separated data in a database. Split the data out and store it correctly.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 22, 2012 at 5:00 am
Jeff Moden (5/22/2012)
It's all going to suck for performance. Don't store comma separated data in a database. Split the data out and store it correctly.
If there are CPU cycles available and no one to use them, would anyone notice a performance-sucking SQL statement? :w00t:
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 22, 2012 at 5:22 am
dwain.c (5/21/2012)
If you're trying to join the two tables based on common names, you can also use PATINDEX.
Create table #basetable(Id int identity, Component varchar(256))
Create table #dailytable(ID Int, FILENAME Char(50), PROCESS Char(50))
insert into #basetable Values( 'ravi,raja ')
insert into #basetable Values( 'raja,jim,sham')
insert into #basetable Values( 'prem,john,pal,ram')
insert into #basetable Values( 'ramindra,john,pal')
Insert into #dailytable values(1, 'In' ,'Ram ')
Insert into #dailytable values(2, 'out' ,'prem')
Insert into #dailytable values(3, 'In-out ', 'Ravi' )
SELECT *
FROM #basetable
INNER JOIN #dailytable
ON PATINDEX('%,' + RTRIM(Process), Component) <> 0 OR
PATINDEX(RTRIM(Process) + ',%', Component) <> 0
DROP TABLE #basetable, #dailytable
You may also have to use the 3rd case described above for LIKE, depending on your data.
Dwain, what happens when the items are not at the beginning or end of the comma list in your code?
e.g.
Try it with this: -
CREATE TABLE #basetable (Id INT identity, Component VARCHAR(256));
CREATE TABLE #dailytable (ID INT, FILENAME CHAR(50), PROCESS CHAR(50));
INSERT INTO #basetable VALUES ('ravi,ram'); --Should join to 1 and 3
INSERT INTO #basetable VALUES ('ravi,prem,ram'); --Should join to 1,2 and 3
INSERT INTO #basetable VALUES ('prem,ram'); --Should join to 1 and 2
INSERT INTO #basetable VALUES ('ravi,prem,random,otherstring,ram'); --Should join to 1,2 and 3
INSERT INTO #dailytable VALUES (1, 'In', 'Ram');
INSERT INTO #dailytable VALUES (2, 'out', 'prem');
INSERT INTO #dailytable VALUES (3, 'In-out', 'Ravi');
Your PATINDEX returns: -
ID FILENAME PROCESS Id Component
----------- -------------------------------------------------- -------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 In Ram 1 ravi,ram
1 In Ram 2 ravi,prem,ram
1 In Ram 3 prem,ram
1 In Ram 4 ravi,prem,random,otherstring,ram
2 out prem 3 prem,ram
3 In-out Ravi 1 ravi,ram
3 In-out Ravi 2 ravi,prem,ram
3 In-out Ravi 4 ravi,prem,random,otherstring,ram
May 22, 2012 at 5:26 am
Thank you for your suggestions. The database in designed in such a way that it has to store comma separated values and this can not be changed now.
May 22, 2012 at 5:32 am
Cadavre (5/22/2012)
Dwain, what happens when the items are not at the beginning or end of the comma list in your code?
Doesn't work obviously! Note the statement at the end of my post, which I put there suspecting that the data in the catenated string may not be very well formed. So I got a bit lazy instead of trying to take the solution to the n-th level.
Might be able to get it there with a little tweaking, but the truth of the matter is my opinion corresponded to Jeff's I was just too polite to say it, knowing that the OP would just come back with "but I can't change it."
I've seen too much really poor database structure lately, so while my heart was in the right place, my brain simply rebelled at the notion of supporting such a thing.
OK. Rant over.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 22, 2012 at 5:41 am
dwain.c (5/22/2012)
Doesn't work obviously! Note the statement at the end of my post, which I put there suspecting that the data in the catenated string may not be very well formed. So I got a bit lazy instead of trying to take the solution to the n-th level.Might be able to get it there with a little tweaking, but the truth of the matter is my opinion corresponded to Jeff's I was just too polite to say it, knowing that the OP would just come back with "but I can't change it."
I've seen too much really poor database structure lately, so while my heart was in the right place, my brain simply rebelled at the notion of supporting such a thing.
OK. Rant over.
It's easy enough to fix, but last time I pointed out that I thought you'd missed something you seemed slightly ticked off so I thought I'd try something different this time 😀
Anyway, if you look at what michael vessey posted: -
SELECT *
FROM #basetable
WHERE component LIKE 'ram,%'
OR component LIKE '%,ram,%'
OR component LIKE '%,ram';
All your PATINDEX was missing is the "middle" bit.
If we include it, then all works fine again: -
SELECT *
FROM #basetable a
INNER JOIN #dailytable b ON PATINDEX('%,' + RTRIM(Process), Component) <> 0
OR PATINDEX(RTRIM(Process) + ',%', Component) <> 0
OR PATINDEX('%,' + RTRIM(Process) + ',%', Component) <> 0;
May 22, 2012 at 6:01 am
i cannot change the design in the sense, other application are using the same database and which will store the data in a comma separated format.
In the join if we add one more condition its working for all options
OR PATINDEX('%,' + RTRIM(Process)+ ',%', Component) <> 0
Thank You,
carthik
May 22, 2012 at 12:07 pm
Is this another correct aproach?
SELECT *
FROM #basetable a
INNER JOIN #dailytable b ON a.Component LIKE '%'+b.Process+'%'
May 22, 2012 at 12:13 pm
It is possible to structure the tables such that you eliminate the column storing the data as a comma separated list. You can then hide this structural change behind a view that rejons the data appropriately and presents it to the applications just as the current table.
It may sound easy, but there is actually quite a bit of work needed to make it work behind the seances so that there are no application changes needed.
May 22, 2012 at 6:28 pm
Cadavre (5/22/2012)
It's easy enough to fix, but last time I pointed out that I thought you'd missed something you seemed slightly ticked off so I thought I'd try something different this time 😀
Cadavre - Definitely not ticked off at you and didn't mean to sound that way. It's just that I am saddened by the frequency with which I'm confronted with such poor design and the same lame excuse.
Lest I be accused of offering a poor solution to a dicey question, I offer this in exchange:
CREATE TABLE #basetable (Id INT identity, Component VARCHAR(256));
CREATE TABLE #dailytable (ID INT, FILENAME CHAR(50), PROCESS CHAR(50));
INSERT INTO #basetable VALUES ('ravi,ram'); --Should join to 1 and 3
INSERT INTO #basetable VALUES ('ravi,prem,ram'); --Should join to 1,2 and 3
INSERT INTO #basetable VALUES ('prem,ram'); --Should join to 1 and 2
INSERT INTO #basetable VALUES ('ravi,random,premiere,ram'); --Should join to 1,2 and 3
INSERT INTO #dailytable VALUES (1, 'In', 'Ram');
INSERT INTO #dailytable VALUES (2, 'out', 'prem');
INSERT INTO #dailytable VALUES (3, 'In-out', 'Ravi');
SELECT *
FROM #basetable
INNER JOIN #dailytable
ON PATINDEX('%,' + REPLACE(Process,' ','') + ',%', ','+ REPLACE(Component, ' ', '') + ',') <> 0
DROP TABLE #basetable, #dailytable
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply