Can you search for an apostrophe in a text string 2

[sewilj]

Is the above possible?????

Lewis
United Kingdom

 

[SQLBill]

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

 

[sewilj]

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

 

[SQLBill]

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

 

[sewilj]

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

 

[sewilj]

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

 

[JamesLean]

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

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

--James

 

[SQLBill]

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