Solved

better way than like

Posted on 2008-10-07
7
168 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

739 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