Replace function in SQL - 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

SQL Server Development (2000)

Replace function in SQL

Author

Topic

Rajee
Starting Member

12 Posts

Posted - 2006-08-23 : 04:36:58

Hi,
i have a string variable and Iam trying to replace '|' with single quotes inside a stored procedure using Replace function of SQL Server.
For example
@Temp='abc|cde|'
select @Temp=Replace(@Temp,'|',how to specify single quote as the second argument)
Thanks
Rajee

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-23 : 04:40:03

select @Temp=Replace(@Temp,'|','''')

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Rajee
Starting Member

12 Posts

Posted - 2006-08-23 : 07:39:57

Thanks for your reply. But i want to replace it with single quotes and not with two single quotes.Guess if i give u the exact requirement, it will be clear. Iam trying for something like this
qry='Select * from table where field1 like |s%|'+
' and field2 != ||'

Now i need to replace the | symbol with single quotes to make the query as
Select * from table where field1 like 'S%' and field2 !=''

Then i need to execute the query as
exec(qry)
But how do achieve the replace step in between is my doubt.
Thanks for your effort.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-23 : 08:00:00

Somthing like this


Declare @Qry varchar(100)
set @qry='Select * from table where field1 like |s%|'+
' and field2 != ||'

Select @Qry = Replace(@Qry, '|','''')

print @Qry

Chirag

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-23 : 09:21:27

if you had even tried the thing i suggested you'd see that it does exactly what you wanted.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-23 : 09:32:44