Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Can you search for an apostrophe in a text string 2

Status
Not open for further replies.

sewilj

Technical User
Joined
Apr 30, 2003
Messages
53
Location
GB
Is the above possible?????

Lewis
United Kingdom
 
Yes. Let's say your string is in a column called mystring. This command will return the 'place' where there's an apostrophe.

SELECT PATINDEX('%''%', mystring)

To test it, try this:

DECLARE @mystring VARCHAR(5)
SET @mystring 'ab''cd)
SELECT @mystring
SELECT PATINDEX('%''%', @mystring)

The first SELECT will return ab'cd
The second SELECT will return 3 (because the ' is in the third position).

-SQLBill
 
SQLBill

I have tried this by copying it to Query analyzer v7 but i get the following

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'ab'cd)
SELECT @mystring
SELECT PATINDEX('.
Server: Msg 105, Level 15, State 1, Line 5
Unclosed quotation mark before the character string ', @mystring)

Any ideas? All I want to find is anything such as "can't" within the string.



Lewis
United Kingdom
 
Did you type it EXACTLY as this:

DECLARE @mystring VARCHAR(5)
SET @mystring 'ab''cd)
SELECT @mystring
SELECT PATINDEX('%''%', @mystring)

That's TWO SINGLE quotes between the b and c and between the two %'s.

-SQLBill
 
SQLBill

I actually copied and pasted it from the post. Does it have anything to do with the signle quote at the start of 'ab''cd?

Lewis
United Kingdom
 
SQLBill

I have changed the query to

DECLARE @mystring VARCHAR(5)
SET @mystring = 'ab''cd'
SELECT @mystring
SELECT PATINDEX('%''%', @mystring)

This works as you described. This gives me what i need.

Thanks for your help.

Lewis
United Kingdom
 
FYI, if you're just searching fields for a value then you can use LIKE:

Code:
SELECT col FROM tbl
WHERE col LIKE '%''%'

--James
 
Lewis,

Congrats on catching the actual error since I missed it twice. That close parenthesis ')' at the end of 'ab''cd) should have been a single apostrophe.

Oh well, it's been a loooooonnng day.

-SQLBill
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top