Search for a substring within a string

Hi,

I have the below stored procedure that needs to have a where condition that returns the project's detail where the Rep string contains the passed rep value, @rep.

Rep is a string of several names comma delimited.

The result seems to half work.  If Rep starts with @rep, then a value is returned although @rep can be at any part of the string.
USE [CRM]
GO
/****** Object:  StoredProcedure [dbo].[SelectRepDashboardGrid]    Script Date: 04/21/2011 13:42:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SelectRepDashboardGrid]
	-- Add the parameters for the stored procedure here	
	@rep varchar(100),	
	@status varchar(100)
	
AS 
BEGIN

WITH RepCTE(ProjectID, ProjectState, Rep, ProjectDate, Project, CurrentStatus, DesignerName, ConsultantCompany, Builder, Contractor, Wholesaler, PValue, FollowUpDate, PercWin, Forcasted, SpecifiedValue)
AS
(SELECT DISTINCT P.ProjectID, PS.ProjectState, dbo.fnRepsByProject(P.ProjectID, P.ProjectID) AS Rep, P.ProjectDate, P.Project, S.CurrentStatus, D.DesignerFirstName + ' ' + D.DesignerLastName AS DesignerName, P.ConsultantCompany, dbo.fnBuildersByProject(P.ProjectID, P.ProjectID) AS Builder, dbo.fnContractorsByProject(P.ProjectID, P.ProjectID) AS Contractor, dbo.fnWholesalersByProject(P.ProjectID, P.ProjectID) AS Wholesaler, P.PValue, P.FollowUpDate, P.PercWin, P.Forcasted, P.SpecifiedValue 
FROM ((((Projects P LEFT JOIN ProjectState PS ON (P.ProjectID = PS.ProjectID)) LEFT JOIN Builders B ON (P.ProjectID = B.ProjectID)) LEFT JOIN Contractors C ON (P.ProjectID = C.ProjectID)) LEFT JOIN Wholesalers W ON (P.ProjectID = W.ProjectID)) LEFT JOIN CurrentStatus S ON (P.ProjectID = S.ProjectID) LEFT JOIN SummaryContacts SC ON (P.ProjectID = SC.ProjectID) 
LEFT JOIN Designers D ON (P.DesignerID = D.DesignerID) 
WHERE S.CurrentStatus = @status) 

SELECT ProjectID, ProjectState, Rep, ProjectDate, Project, CurrentStatus, DesignerName, ConsultantCompany, Builder, Contractor, Wholesaler, PValue, FollowUpDate, PercWin, Forcasted, SpecifiedValue
FROM RepCTE
WHERE Rep LIKE ('%' + @rep + '%')

Open in new window

glitAsked:
Who is Participating?
 
Ephraim WangoyaCommented:

first create the funtion to split the list
 
ALTER FUNCTION [dbo].[fnGetCharTable](@list varchar(MAX))
   RETURNS @tbl TABLE (ID varchar(50)) AS
BEGIN
   DECLARE @pos        int,
           @nextpos    int,
           @valuelen   int

   SELECT @pos = 0, @nextpos = 1

   WHILE @nextpos > 0
   BEGIN
      SELECT @nextpos = charindex(',', @list, @pos + 1)
      SELECT @valuelen = CASE WHEN @nextpos > 0
                              THEN @nextpos
                              ELSE len(@list) + 1
                         END - @pos - 1
      INSERT @tbl (ID)
         VALUES (ltrim(rtrim(substring(@list, @pos + 1, @valuelen))))
      SELECT @pos = @nextpos
   END
   RETURN
END

Open in new window


then use the result in your query as follows
 
USE [CRM]
GO
/****** Object:  StoredProcedure [dbo].[SelectRepDashboardGrid]    Script Date: 04/21/2011 13:42:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SelectRepDashboardGrid]
	-- Add the parameters for the stored procedure here	
	@rep varchar(100),	
	@status varchar(100)
	
AS 
BEGIN

WITH RepCTE(ProjectID, ProjectState, Rep, ProjectDate, Project, CurrentStatus, DesignerName, ConsultantCompany, Builder, Contractor, Wholesaler, PValue, FollowUpDate, PercWin, Forcasted, SpecifiedValue)
AS
(SELECT DISTINCT P.ProjectID, PS.ProjectState, dbo.fnRepsByProject(P.ProjectID, P.ProjectID) AS Rep, P.ProjectDate, P.Project, S.CurrentStatus, D.DesignerFirstName + ' ' + D.DesignerLastName AS DesignerName, P.ConsultantCompany, dbo.fnBuildersByProject(P.ProjectID, P.ProjectID) AS Builder, dbo.fnContractorsByProject(P.ProjectID, P.ProjectID) AS Contractor, dbo.fnWholesalersByProject(P.ProjectID, P.ProjectID) AS Wholesaler, P.PValue, P.FollowUpDate, P.PercWin, P.Forcasted, P.SpecifiedValue 
FROM ((((Projects P LEFT JOIN ProjectState PS ON (P.ProjectID = PS.ProjectID)) LEFT JOIN Builders B ON (P.ProjectID = B.ProjectID)) LEFT JOIN Contractors C ON (P.ProjectID = C.ProjectID)) LEFT JOIN Wholesalers W ON (P.ProjectID = W.ProjectID)) LEFT JOIN CurrentStatus S ON (P.ProjectID = S.ProjectID) LEFT JOIN SummaryContacts SC ON (P.ProjectID = SC.ProjectID) 
LEFT JOIN Designers D ON (P.DesignerID = D.DesignerID) 
WHERE S.CurrentStatus = @status) 

SELECT ProjectID, ProjectState, Rep, ProjectDate, Project, CurrentStatus, DesignerName, ConsultantCompany, Builder, Contractor, Wholesaler, PValue, FollowUpDate, PercWin, Forcasted, SpecifiedValue
FROM RepCTE
WHERE Rep in (select ID from from dbo.fnGetCharTable(@rep)

Open in new window

0
 
Pratima PharandeCommented:
your query is correct for like '%%'

can you directly run in query anlyser and chek what ouput you are geeting ?

SELECT ProjectID, ProjectState, Rep, ProjectDate, Project, CurrentStatus, DesignerName, ConsultantCompany, Builder, Contractor, Wholesaler, PValue, FollowUpDate, PercWin, Forcasted, SpecifiedValue
FROM RepCTE
WHERE Rep LIKE ('%' + @rep + '%')
0
 
Om PrakashCommented:
If @rep is comma separated list of strings then you probably need dynamic statement. Also if you want to seach using like operator for all the strings separated by comma, then you need to insert the data in temp table and dynamically create SQL statement with "OR" statement.

If it an exact match then you can simple use the dynamic SQL statement by addind single quotes in the @rep string for each comma separated list.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
John ClaesSenior .Net Consultant & Technical AnalistCommented:
i woudld suggest the replace so you so you create an OR list of values
Due this repleace you'll need tho work with a dynamic query

Example

declare @sql nvarchar(4000)
set @sql ='select * from RepCTE
where Rep LIKE (''%' + replace(@rep, ',','%'') OR Rep LIKE (''%')  + '%'') '

print  @sql
exec (@sql)
0
 
OP_ZaharinCommented:
- a LIKE statement that search for a name like John works as follows:
WHERE Rep LIKE '%John%'

- a LIKE statement doesn't work with commas in between records if you are searching for (John, Sam, Lance) as pointed by om_prakash.
0
 
sureshbabukrishCommented:
create a split function
CREATE FUNCTION SPLIT (
@str_in VARCHAR(8000),
@separator VARCHAR(4) )
RETURNS @strtable TABLE (strval VARCHAR(8000))
AS
BEGIN

DECLARE
@Occurrences INT,
@Counter INT,
@tmpStr VARCHAR(8000)

SET @Counter = 0
IF SUBSTRING(@str_in,LEN(@str_in),1) <> @separator
SET @str_in = @str_in + @separator

SET @Occurrences = (DATALENGTH(REPLACE(@str_in,@separator,@separator+'#')) - DATALENGTH(@str_in))/ DATALENGTH(@separator)
SET @tmpStr = @str_in

WHILE @Counter <= @Occurrences
BEGIN
SET @Counter = @Counter + 1
INSERT INTO @strtable
VALUES ( SUBSTRING(@tmpStr,1,CHARINDEX(@separator,@tmpStr)-1))

SET @tmpStr = SUBSTRING(@tmpStr,CHARINDEX(@separator,@tmpStr)+1,8000)

IF DATALENGTH(@tmpStr) = 0
BREAK

END
RETURN
END


select
*....
from
urtable
inner join ( select strval from SPLIT()) on strval like '%urtable.repname%'
0
 
John ClaesSenior .Net Consultant & Technical AnalistCommented:
if you receive only a comma seperated list of values I would realy suggest a simple replace while creating the SQL-Statement and then running it.

this example is tested and performs the Job asked

Example

declare @sql nvarchar(4000)
set @sql ='select * from RepCTE
where Rep LIKE (''%' + replace(@rep, ',','%'') OR Rep LIKE (''%')  + '%'') '

exec (@sql)
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.