Solved

better way than like

Posted on 2008-10-07
7
163 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 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Where clause in stored procedure 8 56
SSRS report parameters set after publishing to report manager 1 56
Set the max value for a column 7 38
Need help with a query 6 74
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…
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 …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.

809 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