[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Search for a substring within a string

Posted on 2011-04-20
7
Medium Priority
?
381 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:glit
7 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35438715
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
 
LVL 22

Expert Comment

by:Om Prakash
ID: 35438770
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
 
LVL 10

Expert Comment

by:John Claes
ID: 35438895
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35439033
- 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
 
LVL 9

Expert Comment

by:sureshbabukrish
ID: 35440448
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
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 2000 total points
ID: 35441429

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

Expert Comment

by:John Claes
ID: 35446229
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

830 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