Query Fails to return record containing /

Must be something else going on.  The '/' in the data shouldn't cause an issue:

SELECT CASE WHEN 'Main/Second' LIKE '%Main%' THEN 'Match' ELSE 'NoMatch' END

Maybe you're looking for ' Main', i.e. SPACE(1) + 'Main', or something similar?

I wonder if this issue is how the parameter value is passed from the application.

Hi

I'm having issues trying to replicate your problem

use EE
go

if object_id( N'tempdb..#Address', N'U' ) is not null 
	drop table #Address;
	
create table #Address(
	LocStreet varchar( 20 )
	)
	
insert #Address( LocStreet ) 
	values( 'Main' )
		, ( 'Main/Second' )
;

select *
from #Address
;

declare @Street varchar( 20 )
set @Street = 'Main'

select a.*
from #Address a
where
	1 = 1
	-- from the question
	AND a.LocStreet like '%' + @Street + '%'
;

Select allOpen in new window

And the second set of results are what you are requesting

LocStreet
--------------------
Main
Main/Second

(2 rows affected)

Select allOpen in new window

Can you give the definition of LocStreet - I've used varchar( 20 ). There can be some subtle differences between char and varchar etc. And what collation are you using? My default collation is Latin1_General_CI_AS for most of what I do in this part of the world.

Regards
  David

Using Collation SQL_Latin1_General_CP1_CI_AS
They have the  @Street set to @Street nvarchar(MAX) in the query but the actual table field is nvarchar(50).

David I ran your query and it does return the proper value.

Ok here is the full query that I have been modifying

DECLARE @PlaceCode nvarchar(MAX), @StreetNumber nvarchar(MAX), @StreetPrefix nvarchar(50), @Street nvarchar(MAX), @City nvarchar(MAX),  @myAgencyOnly nvarchar(8), @agency nvarchar(16)

--SET @StreetNumber = '[PSIMSQQ Number(Format: nn..)]';
--SET @StreetPrefix = '[PSIMSQQ Prefix(Format: nn..)]';
--SET @Street = '[PSIMSQQ Street(Format: nn..)]';
--SET @City = '[PSIMSQQ City(Format: nn..)]';
--SET @agency = '[PSIMS_AGENCY]';

SET @StreetNumber = '';
SET @StreetPrefix = '';
SET @Street = 'Water';
SET @City = '';
SET @agency = 2;
SET @myAgencyOnly = '[PSIMSQQ Checkbox(Format: My Agency Only)]';

SELECT 
'<A href=[PSIMS]VIEWCASE=' + d.DepartmentShortTitle + ':' + right(CAST(a.CaseYear as varchar), 2) + '-' + right('000000' + CAST(a.CaseID as varchar), 6) + '>' + d.DepartmentShortTitle + ':' + right(CAST(a.CaseYear as varchar), 2) + '-' + right('000000' + CAST(a.CaseID as varchar), 6) + '</A>' as 'Case ID', 
a.locstreetnumber + ' ' + a.locstreetprefix + ' ' + a.locstreet + ' ' + a.LocStreetSuffix + ', ' + a.LocCity + ' ' + a.LocState + ' ' + a.LocZip as 'Address',
a.LocPhone as 'Phone'
FROM CaseMaster a, ListPlaceCodes b, Agency d
WHERE a.LocStreetNumber like @StreetNumber + '%'
AND a.LocStreetPrefix like @StreetPrefix + '%'
AND a.LocStreet like '%' + @Street + '%'
AND a.LocCity like @City + '%'
AND ([PSIMSQQ $QQSELECTLIST_Place_Codes(ListPlaceCodes)] = '' OR b.Code in ([+PSIMSQQ $QQSELECTLIST_Place_Codes(ListPlaceCodes)]))

AND a.LocPlaceCode = b.Description
AND a.agency = CASE WHEN @myAgencyOnly = 'on' THEN @agency ELSE a.Agency END
and a.agency = d.AgencyID
Group by '<A href=[PSIMS]VIEWCASE=' + d.DepartmentShortTitle + ':' + right(CAST(a.CaseYear as varchar), 2) + '-' + right('000000' + CAST(a.CaseID as varchar), 6) + '>' + d.DepartmentShortTitle + ':' + right(CAST(a.CaseYear as varchar), 2) + '-' + right('000000' + CAST(a.CaseID as varchar), 6) + '</A>', a.LocStreetNumber, a.LocStreetPrefix, a.LocStreet, a.LocStreetSuffix, a.LocUnit, a.LocCity, a.LocState, a.LocZip, a.LocPhone
ORDER BY '<A href=[PSIMS]VIEWCASE=' + d.DepartmentShortTitle + ':' + right(CAST(a.CaseYear as varchar), 2) + '-' + right('000000' + CAST(a.CaseID as varchar), 6) + '>' + d.DepartmentShortTitle + ':' + right(CAST(a.CaseYear as varchar), 2) + '-' + right('000000' + CAST(a.CaseID as varchar), 6) + '</A>', a.LocStreetNumber, a.LocStreetPrefix, a.LocStreet, a.LocStreetSuffix, a.LocUnit, a.LocCity

Select allOpen in new window

.

Hi

Still works for me

use EE
go

if object_id( N'tempdb..#Address', N'U' ) is not null 
	drop table #Address;
	
create table #Address(
	LocStreet nvarchar( 50 ) collate SQL_Latin1_General_CP1_CI_AS
	)
	
insert #Address( LocStreet ) 
	values( N'Main' )
		, ( N'Main/Second' )
;

select *
from #Address
;

declare @Street nvarchar( max ) 
set @Street = N'Main' collate SQL_Latin1_General_CP1_CI_AS

select a.*
from #Address a
where
	1 = 1
	-- from the question
	AND a.LocStreet like '%' + @Street + '%'
;

Select allOpen in new window

Suggests that there may be a hidden or special character somewhere

Regards
  David

ok i ran a test for hidden chars using the following funtion:

CREATE FUNCTION dbo.ShowWhiteSpace (@str varchar(8000))
RETURNS varchar(8000)
AS
BEGIN
     DECLARE @ShowWhiteSpace varchar(8000);
     SET @ShowWhiteSpace = @str
     SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(32), '[?]')
     SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(13), '[CR]')
     SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(10), '[LF]')
     SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(9),  '[TAB]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(1),  '[SOH]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(2),  '[STX]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(3),  '[ETX]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(4),  '[EOT]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(5),  '[ENQ]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(6),  '[ACK]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(7),  '[BEL]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(8),  '[BS]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(11), '[VT]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(12), '[FF]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(14), '[SO]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(15), '[SI]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(16), '[DLE]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(17), '[DC1]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(18), '[DC2]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(19), '[DC3]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(20), '[DC4]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(21), '[NAK]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(22), '[SYN]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(23), '[ETB]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(24), '[CAN]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(25), '[EM]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(26), '[SUB]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(27), '[ESC]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(28), '[FS]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(29), '[GS]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(30), '[RS]')
--   SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(31), '[US]')
     RETURN(@ShowWhiteSpace)
END

Select allOpen in new window

SELECT dbo.ShowWhiteSpace(locstreet) from CaseMaster where caseyear = 2018 and caseid = 3743  (The case in question)

It returned VINE[?]ST/WATER[?]ST

When stringing together columns like that, should be checking for NULL

e.g. instead of : a.locstreetnumber + ' ' + a.locstreetprefix + ' ' + a.locstreet + ' ' + a.LocStreetSuffix +
try :  isnull(a.locstreetnumber,'') + ' ' + isnull(a.locstreetprefix,'') + ' ' + isnull(a.locstreet,'') + ' ' + isnull(a.LocStreetSuffix,'') +

Any NULL will render the entire string to be NULL.

Thanks to everyone for trying to assist with this issue.

Just wondering if it thinks it is an escape character type of thing....

Read : https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/like-predicate-escape-character?view=sql-server-2017

So, might be worth specifying an escape character '/' or '~'

Read : https://docs.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-2017

Have a look at these quick tests (try to predict the outcome before you execute - it might surprise - especially test4)

-- test 1
declare @street varchar(20) = 'w'
select * 
from (values ('V/Water'),('wine'),('vine')) S(Street)
where Street like '%'+@street+'%'  

-- test 2
declare @street varchar(20) = 'w'
select * 
from (values ('V/Water'),('wine'),('vine')) S(Street)
where Street like '%'+@street+'%'  collate SQL_Latin1_General_CP1_CS_AS  --  the _CS_ denotes case sensitive  or _CI_ for case insensitive  

-- test 3
declare @street varchar(20) = '/w'
select * 
from (values ('V/Water'),('wine'),('vine')) S(Street)
where Street like '%'+@street+'%' escape '~'  


-- test 4
declare @street varchar(20) = '/w'
select * 
from (values ('V/Water'),('wine'),('vine')) S(Street)
where Street like '%'+@street+'%' escape '/'  

Select allOpen in new window

Mark,

I played around with those tests.  They all worked.  There must be something wrong with that specific record.  I'll be going back to investigate further when I can.

Cheers Mike, thanks for getting back, and good luck with your investigations.