better way than like

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
LVL 25
dgrafxAsked:
Who is Participating?
 
BrandonGalderisiConnect With a Mentor Commented:
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
 
Patrick MatthewsCommented:
Hello dgrafx,

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

Regards,

Patrick
0
 
dgrafxAuthor Commented:
???
select whatever
from aTable
where ##theproblemcodeposted##

basicly - I'm pulling records where @ParentID is in c.ParentID list
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
BrandonGalderisiCommented:
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
 
BrandonGalderisiCommented:
"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
 
dgrafxAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.