Solved

better way than like

Posted on 2008-10-07
7
169 Views
Last Modified: 2012-05-05
been messing with some code

this does exactly what I want
(',' + c.ParentID + ',' LIKE '%,'+ @ParentID +',%')
but don't want to use LIKE

this doesn't work - doesn't return records where there are more than one in the list:
@ParentID IN (c.ParentID)

this doesn't work - it times out:
@ParentID IN (Select value from dbo.ParmsToList(c.ParentID,','))
dbo.parmstolist is a function to turn a list like 2,5,7 into '2','5','7'

help

c.ParentID is a varchar column whose value might be "2,5,7"
@ParentID might be 5
0
Comment
Question by:dgrafx
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22661502
Hello dgrafx,

Please have a go at explaianing what you're trying to accomplish.

Regards,

Patrick
0
 
LVL 25

Author Comment

by:dgrafx
ID: 22661589
???
select whatever
from aTable
where ##theproblemcodeposted##

basicly - I'm pulling records where @ParentID is in c.ParentID list
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22661597
We've gone over this on your previous posting.  But I have an idea now.  Letme know if this works.

select * from yourTable c
join [dbo].[fn_DelimitedToTable] (@ParentIdList,',') y
  on c.parentid like '%'+y.theValue+'%'


And here's the code for the funciton:



if object_id('[dbo].[fn_DelimitedToTable]') is not null
     drop function [dbo].[fn_DelimitedToTable]
go
create function [dbo].[fn_DelimitedToTable](@DelimitedString nvarchar(max), @Delimiter nvarchar(32))
returns @Values TABLE
     (ident         int not null identity primary key clustered
     ,thePosition   int not null
     ,theValue      nvarchar(max)
     )
as
/************************************************************
*
*    Author:        Brandon Galderisi
*    Last modified: 07-Oct-2008
*    Purpose:       splits an input string (@DelimitedString) 
*                   on a delimiter (@delimiter) and outputs 
*                   a table of values.
*    
*
*************************************************************/
begin
 
insert into @Values (thePosition,theValue)
		select n, substring(@delimiter + @DelimitedString + @delimiter, n + (datalength(@delimiter)/2), charindex(@delimiter, @delimiter + @DelimitedString + @delimiter, n + datalength(@delimiter)/2) - n - datalength(@delimiter)/2) as string_value
		from	dbo.vw_Nums
		where
			n <= (datalength(@delimiter + @DelimitedString + @delimiter)/2) - (datalength(@delimiter)/2)
			and substring(@delimiter + @DelimitedString + @delimiter, n, (datalength(@delimiter)/2)) = @delimiter
 
 
 
return
end
/*
-- The purpose of vw_Nums is if the source database does not have a numbers table.  This view (vw_nums)
-- will allow the parsing of up to 4 billion character strings with the above function.  Whether a static
-- table or this view is used for fn_DelimitedToTable, it can only split a string as long as the source 
-- numbers table.
Requires a nunbers table or this view:
create view vw_Nums
as
with   cte0 as (select 1 as c union all select 1), 
       cte1 as (select 1 as c from cte0 a, cte0 b), 
       cte2 as (select 1 as c from cte1 a, cte1 b), 
       cte3 as (select 1 as c from cte2 a, cte2 b), 
       cte4 as (select 1 as c from cte3 a, cte3 b), 
       cte5 as (select 1 as c from cte4 a, cte4 b), 
       nums as (select row_number() over (order by c) as n from cte5)
       select n from nums 
 
 
 
Sample Usage:
select * from [dbo].[fn_DelimitedToTable]('a|%25basdf|%25c|%25d','|%25')
select theValue from [dbo].[fn_DelimitedToTable]('a','|')
select * from [dbo].[fn_DelimitedToTable]('a basdf c d',' ')
*/
GO

Open in new window

0
Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22661615
"basicly - I'm pulling records where @ParentID is in c.ParentID list"

To clarify for matthewspatrick's understanding.  (s)he's not just pulling for what's in the list, but if a parentid is 99304, and the list is '99922,99822,993', it should return because 99304 is LIKE 993.
0
 
LVL 25

Author Comment

by:dgrafx
ID: 22661749
brandonG - you must have me mistaken for someone else!
there was no previous post by me that you refer to.

start over.
This is a self-referencing table where we have CatID int, ParentID varchar
The @ParentID is actually a CatID "sent in"
and I need results based on @ParentID being in the ParentID column
3 (for ex) ONLY matches a ParentID of '3' or 'somethingelse,3,somethingelse'
it does Not match 30 or 23 et cetera
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 250 total points
ID: 22662410
Sorry, major deja vu.  Do you work with someone that also has an EE and perhaps on the same section of code right now.  Because this all seems VERY familiar.


:)



Anyway:

help

c.ParentID is a varchar column whose value might be "2,5,7"
@ParentID might be 5

If you were searching for 5, given the search list of 2,5,7 it would be easy.  But you are going to be stuck using the like to search.  No way around it.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 22662529
>this doesn't work - it times out:
>@ParentID IN (Select value from dbo.ParmsToList(c.ParentID,','))

if this is SQL 2005, please try this:
SELECT ...
  FROM yourtable c
  CROSS APPLY dbo.ParmsToList(c.ParentID) l
 WHERE c.Parent LIKE '%'+ @ParentID + '%'   --- this is only to limit the rows processed from yourtable with a first rough filter
   AND @ParentID = l.Value -- this is the actual matching test

Open in new window

0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question