Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1096
  • Last Modified:

Escape ~ character for use in tsql contains() function

Hi,

I'm using SQL server 2000 and I want to search for records that contain the ~ character in the text. Something like this:

select * from tblText where contains(txt, '~')

Apparently that will not work as ~ character is actually the NEAR operator.

How can I escape it? I wouldn't want to use LIKE expresssion as I want to take advantage of contains() and  its full text index.

Thanks a lot.
0
Squarenine
Asked:
Squarenine
  • 2
2 Solutions
 
milduraitCommented:
~ is ascii character 126

Therefore you can use
SELECT * FROM tblText WHERE contains(txt,char(126))
0
 
SquarenineAuthor Commented:
Hi,

Unfortunately, contains(txt,char(126)) causes a syntax error.

I finally went with::

select * from tblText where charindex('~', txt') > 0

and it works great.

Still want to know how to escape ~ in contains function though.

Thanks anyway.
0
 
SquarenineAuthor Commented:
ok
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now