How to escape ' character - 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

SQL Server 2000 Forums

Transact-SQL (2000)

How to escape ' character

Author

Topic

overthetop
Starting Member

18 Posts

Posted - 2006-05-17 : 16:23:08

I want to escape this character '. How can I do this.

I have something like this:

exec('select ....
where [Name] like '%' + @Name + '%' ....')

Please help.

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-05-17 : 16:57:35

Throw more ' in there.

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-05-17 : 23:10:22

ergo, exec('select .... where [Name] like ''%''' + @Name + '''%''' + '....')

May the Almighty God bless us all!

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2006-05-17 : 23:20:17

quote:
Originally posted by overthetop

I want to escape this character '. How can I do this.

Run like hell

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

overthetop
Starting Member

18 Posts

Posted - 2006-05-18 : 04:30:14

Thanks a lot :)

here is the final

declare @Color nvarchar(50)

set @Color = 'B'

exec(
'select * from Production.Product
where [Color] like ' + '''%''' + ' + ''' + @Color + ''' + ' + '''%''')

Kristen
Test

22859 Posts

Posted - 2006-05-18 : 04:42:58

Its sometimes easier use a string expression to "prepare" the SQL, and then execute that - e.g.:


DECLARE	@Color	varchar(100)
SELECT	@Color = 'MyColour'

DECLARE	@strSQL	nvarchar(4000)
SELECT	@strSQL = 
'select * from Production.Product 
where [Color] like ' + '''%''' + ' + ''' + @Color + ''' + ' + '''%'''
SELECT	[DEBUG] = @strSQL

EXEC (@strSQL)

The expression can then be simplifed to:

SELECT @strSQL =
'select * from Production.Product
where [Color] like ''%' + @Color + '%'''

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-18 : 05:10:40

As you dont pass object names as parameter, you can do this without dynamic sql

declare @Color nvarchar(50)

set @Color = 'B'

select * from Production.Product
where [Color] like '%' + @Color + '%'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources