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

• vinu512

  SSCoach

  Points: 15729

  More actions

  January 22, 2013 at 10:06 pm

  #1579897

  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

  January 22, 2013 at 11:34 pm

  #1579907

  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

  January 23, 2013 at 2:53 am

  #1579967

  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: 110206

  More actions

  January 23, 2013 at 3:50 pm

  #1580296

  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

  January 23, 2013 at 10:20 pm

  #1580350

  Thanks,

  So the second one can be more simpler like this:

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

• ScottPletcher

  SSC Guru

  Points: 100942

  More actions

  January 24, 2013 at 8:51 am

  #1580582

  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) A socialist is someone who will give you the shirt off *someone else's* back.

• zombieisdead2020

  SSC-Addicted

  Points: 414

  More actions

  January 24, 2013 at 11:30 am

  #1580657

  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