No Wildcards Characters

Question

No Wildcards Characters

zombieisdead2020

SSC-Addicted

Points: 414
More actions
January 22, 2013 at 8:40 pm

#267539

Hi all,
I want to search for "[[% SQL Server %]]" pattern in a string value (Note, ] [ % are not wildcards).
But I cannot do that with LIKE keyword without ESCAPE clause, How can I fix the first query?
See:
Declare @string varchar(50) = 'abcd [[% SQL Server %]]' -- Matched
/* LIKE []: No Matched */
select case when @string like '%[[][[][%] SQL Server [%][]][]]%'
then'Matched' else 'No Matched' end
/* LIKE ESCAPE: Matched */
select case when @string like '%?[?[?% SQL Server ?%?]?]%' ESCAPE '?'
then'Matched' else 'No Matched' end
/* LEN & REPLACE: Matched */
select case when len(replace(@string,'[[% SQL Server %]]',''))<len(@string)
then'Matched' else 'No Matched' end

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

vinu512 SSCoach Points: 15729 More actions · Answer 1

Hi...its good that you spent time on explaining you requirement...but I am still having problems with understanding it, as there isn't any sample data to see what you are actually doing.

I would suggest that you post some quick sample data...maybe just 5-10 rows and then post the search output you want from the sample data. I bet we can get this working....but only if you help 🙂

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

zombieisdead2020 SSC-Addicted Points: 414 More actions · Answer 2

It's easy no need to sample data, I want an alternative LIKE for this without ESCAPE.

like '%?[?[?% SQL Server ?%?]?]%' ESCAPE '?'

I wrote this code, but that is not correct:

like '%[[][[][%] SQL Server [%][]][]]%'

Gazareth One Orange Chip Points: 27737 More actions · Answer 3

What's wrong with using ESCAPE?

You're along the right lines with the last approach - try using CHARINDEX instead though:

SELECT CHARINDEX('[[% SQL Server %]]', @string)

Sergiy SSC Guru Points: 110208 More actions · Answer 4

zombieisdead2020 (1/22/2013)
How can I fix the first query?

You do not need escape the closing square brackets.

This should work:

Declare @string varchar(50) = 'abcd [[% SQL Server %]]' -- Matched

/* LIKE []: No Matched */

select case when @string like '%[[][[][%] SQL Server [%]]]%'

then'Matched' else 'No Matched' end

_____________
Code for TallyGenerator

zombieisdead2020 SSC-Addicted Points: 414 More actions · Answer 5

Thanks,

So the second one can be more simpler like this:

LIKE '$[$[$% SQL Server $%]]' ESCAPE '$'

ScottPletcher SSC Guru Points: 101101 More actions · Answer 6

declare @search_pattern_should_be varchar(100)

set @search_pattern_should_be = '%[[][[][%] SQL Server [%]]]%'

--example

select case when @string like '%[[][[][%] SQL Server [%]]]%'

then'Matched' else 'No Matched' end

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

zombieisdead2020 SSC-Addicted Points: 414 More actions · Answer 7

ScottPletcher (1/24/2013)
declare @search_pattern_should_be varchar(100) set @search_pattern_should_be = '%[[][[][%] SQL Server [%]]]%' --example select case when @string like '%[[][[][%] SQL Server [%]]]%' then'Matched' else 'No Matched' end

Is not same with Post #1410825