Searching for % - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Searching for %

Author

Topic

Apples
Posting Yak Master

146 Posts

Posted - 2008-12-23 : 17:59:40

I need to be able to search for a percent sign (%) using a stored procedure. My query has a LIKE clause like this:

WHERE text LIKE '%' + @text + '%'

I'm using Microsoft SQL Server 2005.

I tried looking up escape characters, and came across this: [url]http://sqlserver2000.databases.aspfaq.com/how-do-i-search-for-special-characters-e-g-in-sql-server.html[/url]. It says to put brackets around the percent sign.

I tried that, but it isn't working. If I search for:

10% of my income

I receive an error saying:

Syntax error occurred near '['. Expected ''''' in search condition '10[%] of my income'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-23 : 18:06:18

Here's an example:


DECLARE @t1 table(c1 varchar(50))
DECLARE @s varchar(5)

SET @s = '10[%]'

INSERT INTO @t1 VALUES('10%')
INSERT INTO @t1 VALUES('sdf 25% asdfsadf')
INSERT INTO @t1 VALUES('ask;flj')

SELECT c1
FROM @t1
WHERE c1 LIKE '%' + @s + '%'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources