Solved

Escape ~ character for use in tsql contains() function

Posted on 2009-03-29
3
1,062 Views
Last Modified: 2012-05-06
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
Comment
Question by:Squarenine
  • 2
3 Comments
 
LVL 11

Assisted Solution

by:mildurait
mildurait earned 20 total points
ID: 24015276
~ is ascii character 126

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

Accepted Solution

by:
Squarenine earned 0 total points
ID: 24016167
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
 

Author Comment

by:Squarenine
ID: 24016179
ok
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now