Solved

better way than like

Posted on 2008-10-07
7
158 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
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 24

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 24

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 142

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

707 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now