Solved

Where h.category  like '%' + @pcategoryo + '%'   RETURNS NOTHING

Posted on 2014-07-21
12
400 Views
Last Modified: 2014-07-23
SQL2008 R2
Cannot seem to get this Where clause to work in my sql stored procedure. Returns nothing.


where h.category like '%' + @pcategoryo + '%'      


@pcategoryo contains       BUSGR,BUSGY,BUSGT
@pcategoryo is a SSRS report parm
h.category  =  BUSGR
0
Comment
Question by:thayduck
  • 6
  • 3
  • 3
12 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 40209542
You can't use LIKE with a comma-delimited list.  You are effectively looking for the string "BUSGR,BUSGY,BUSGT".

I would recommend using a table-valued split function (there are many examples on the internet...pick one that matches your needs) and join against that return using LIKE.

SELECT ...
FROM myTable AS h
INNER JOIN dbo.Split(@pcategoryo, ',') AS category
   ON h.category LIKE '%' + category.value + '%'
0
 

Author Comment

by:thayduck
ID: 40209582
I do use the table valued split function now, but do not understand why this would not work.
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 40209618
Your query is evaluating to

WHERE h.category LIKE '%BUSGR,BUSGY,BUSGT%'

Open in new window


I would assume that nothing in your table has a category value containing that string.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:thayduck
ID: 40209801
I just assumed that when you surround BUSGR,BUSGY,BUSGT with %, it meant that I should have gotten a match on BUSGR since BUSGR is in that string somewhere.


%

Any string of zero or more characters.

WHERE title LIKE '%computer%' finds all book titles with the word 'computer' anywhere in the book title.

So would not  BUSGR,BUSGY,BUSGT  be like the book title and I am looking for the word BUSGR.

Just confusing....
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 150 total points
ID: 40209916
You'd have to do the LIKE the other way around:

WHERE @pcategoryo + ',' LIKE '%' + h.category + ',%'
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 40209924
It's actually pretty simple you are just assuming functionality that doesn't exist.  How would the LIKE operator know to interpret your string as a delimited list?

What you might be looking for is the IN operator but you would need to use dynamic SQL to make it work and that is generally not the best solution.  The LIKE operator is used to find strings that appear in other strings.  If the parameter is a list of valid category values then you should not be using the LIKE operator at all.  Instead I would recommend using the split function as i mentioned earlier with a slight modification:

SELECT ...
FROM myTable AS h
INNER JOIN dbo.Split(@pcategoryo, ',') AS category
   ON h.category = category.value

Open in new window


For the sake of completeness...

CREATE FUNCTION [dbo].[Split]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE 
AS
RETURN 
(
    WITH Split(stpos,endpos) 
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)
GO

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40209933
For example:

DECLARE @pcategoryo varchar(100)
SET @pcategoryo = 'BUSGR,BUSGY,BUSGT'

SELECT *
FROM (
    SELECT 1 AS row#, 'BUSGR' AS category UNION ALL
    SELECT 2, 'BUSZZ' UNION ALL
    SELECT 3, 'BUSGT'
) AS h
WHERE @pcategoryo + ',' LIKE '%' + h.category + ',%'


Btw, that's an extremely inefficient split.  I wouldn't suggest using it for large data sets.
0
 

Author Comment

by:thayduck
ID: 40209989
Scott, your first suggestion worked just fine with a small change.
It does exactly what I want it to do now.
I take the report parm as is  BUSYE,BUSGR,BUSOK and using the like (%,%)
it includes the records if h.category = any one of the parms chosen by the user in the report parms.

WHERE @pcategoryo LIKE '%' +h.category+%'    

Now I do not have to create or read any temp tables.
Do you guys see any issues in using this code this way instead of creating temp tables ?
I will test some more but it looks good to me...
0
 

Author Closing Comment

by:thayduck
ID: 40212192
This works fine for report type parms that contain a fixed length code.
I just wanted another way to compare report parms without having to create a temp table and splitting of the parm selections into the table then reading the table in a Where clause.

Thanks Scott and Brian.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40212247
>> This works fine for report type parms that contain a fixed length code. <<

I added the delimiter, in this case a comma, to the comparisons allow even variable-length values to be accurately tested.

For example:

DECLARE @pcategoryo varchar(100)
SET @pcategoryo = 'BUSGR,BUSGY,BUSGT,BUSS'

SELECT *
FROM (
    SELECT 1 AS row#, 'BUSGR' AS category UNION ALL
    SELECT 2, 'BUSZZ' UNION ALL
    SELECT 3, 'BUSG' UNION ALL
    SELECT 4, 'BUS' UNION ALL
    SELECT 5, 'BUSS' UNION ALL
    SELECT 6, 'BUSSY'
) AS h
WHERE @pcategoryo + ',' LIKE '%' + h.category + ',%'
0
 

Author Comment

by:thayduck
ID: 40212417
How did I miss that.
Your original code does handle both fixed or variable length parameter codes.
Thanks for pointing that out to me.
0
 

Author Comment

by:thayduck
ID: 40214711
Follow Up:

Even though this clause ( WHERE @pcategoryo + ',' LIKE '%' + h.category + ',%' ) does what I want it to, the performance of the SQL is now terrible.

I have gone back to splitting off the parameters into temp tables then reading that temp table in a IN statement in the WHERE statement.

h.category IN(SELECT categoryid
                           FROM   #category
                           WHERE  h.category = categoryid)    

Much Much Much  FASTER.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
hyperlink data type in SQL 3 28
SQL View nearest date 5 36
performance query 4 24
syntax sql error 2 13
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

777 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