Link to home
Create AccountLog in
Avatar of MikeM670
MikeM670

asked on

Query Fails to return record containing /

Query misses specific type of record.  Using Main as the search criteria.

AND a.LocStreet like '%' + @Street + '%'

Open in new window


Fails to return a record that contains '/'.   Example:  Main/Second

But will return any other record that contains Main.  Example: 110 Main, Main Water Treatment Plant, South Main...ect
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

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?
Avatar of MikeM670
MikeM670

ASKER

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 + '%'
;

Open in new window

And the second set of results are what you are requesting
LocStreet
--------------------
Main
Main/Second

(2 rows affected)

Open 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

Open 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 + '%'
;

Open 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

Open 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.
ASKER CERTIFIED SOLUTION
Avatar of MikeM670
MikeM670

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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 '/'  

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