Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 No Wildcards Characters

Author  Topic 

sigmas
Posting Yak Master

172 Posts

Posted - 2013-01-23 : 01:43:48
Hi all,
I want to search for "[[% SQL Server %]]" pattern in a string value (Note, ] [ % are not wildcards).

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 [%][]][]]%'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-23 : 02:01:38
do you mean this?



SELECT *
FROM
(
SELECT '[[% SQL Server %]]' AS val UNION ALL
SELECT '[[%SQL Server]]' AS val UNION ALL
SELECT 'SQL Server' AS val UNION ALL
SELECT '[% SQL%]'
)t
WHERE val LIKE '%[[[%] SQL Server [%]]%'


output
-----------------------
val
-----------------------
[[% SQL Server %]]





------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

sigmas
Posting Yak Master

172 Posts

Posted - 2013-01-23 : 04:21:45
quote:
Originally posted by visakh16

do you mean this?



SELECT *
FROM
(
SELECT '[[% SQL Server %]]' AS val UNION ALL
SELECT '[[%SQL Server]]' AS val UNION ALL
SELECT 'SQL Server' AS val UNION ALL
SELECT '[% SQL%]'
)t
WHERE val LIKE '%[[[%] SQL Server [%]]]%'


output
-----------------------
val
-----------------------
[[% SQL Server %]]





------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Thank you, that's right. are there some good articls about such pattern matching?

my rewrite query: like '%?[?[?% SQL Server ?%]]%' escape '?'
Go to Top of Page

sigmas
Posting Yak Master

172 Posts

Posted - 2013-01-23 : 06:51:54
Now how I should query for this by using square brackets ([]):
'[abcde]'

Is this correct?
like '[[]abcde]%'

or this?
like '$[abcde]%' escape '$'

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-23 : 07:20:38
yep...that's correct

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

sigmas
Posting Yak Master

172 Posts

Posted - 2013-01-23 : 07:25:26
your like is not correct
Execute this.
SELECT *
FROM
(
SELECT '[% SQL Server %]]' AS val UNION ALL
SELECT '[[%SQL Server%]]' AS val UNION ALL
SELECT '[% SQL Server %]' AS val UNION ALL
SELECT '[ SQL]'
)t
WHERE val like '%[[[%] SQL Server [%]]]%'
Go to Top of Page
   

- Advertisement -