How to find position of a single quote in a string?

MrPeds · Sep 14, 2004

Hi,

This is quite a simple one, but I;d like to know how i find the position of a single quote within a string. For example if the string is:

"shopNo, weekNo, 'Monday' as dayofweek"

I want to get the position of the character just before the M in monday. so this would give position 17?

I am not sure whether to use charindex, or patindex and i dont know how to delimit in these functions.

Thanks,

MrPeds

SQLBill · Sep 14, 2004

Try this:

Code:

SET QUOTED_IDENTIFIER OFF
DECLARE @test VARCHAR(10)
SET @test = "te'st"
SELECT @test
SELECT CHARINDEX("'", @test
SET QUOTED_IDENTIFIER ON

-SQLBill

SQLBill · Sep 14, 2004

Or better yet:

Code:

DECLARE @test VARCHAR(10)
SET @test = 'te''st
SELECT @test
SELECT CHARINDEX('''', @test)

By the way, I forgot a close parenthesis ')' in my post above. It should have been at the end of the line with CHARINDEX.

-SQLBill

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!

How to find position of a single quote in a string?

MrPeds

Programmer

SQLBill

MIS

SQLBill

MIS

Similar threads

Part and Inventory Search

Sponsor