Finding last instance of string

How can I find the last instance of a string? I was thinking of writing a
loop that goes through the string, but that requires writing a few lines of
code. I was also thinking of inverting the string (in this case, charindex
would work since). But I'm not sure how to invert the string w/o writing too
much code.
Any help is appreciated. Thanks.> But I'm not sure how to invert the string w/o writing too
> much code.
Check the REVERSE T-SQL function.
Dejan Sarka|||DECLARE @.s VARCHAR(32);
SET @.s = 'aoodfsdf';
SELECT LastInstanceOfA = CASE CHARINDEX('a', @.s)
WHEN 0 THEN 0
ELSE LEN(@.s) + 1 - CHARINDEX('a', REVERSE(@.s))
END;
"VMI" <VMI@.discussions.microsoft.com> wrote in message
news:109B0CEE-6424-4154-9ED3-7E56C2B41B7F@.microsoft.com...
> How can I find the last instance of a string? I was thinking of writing a
> loop that goes through the string, but that requires writing a few lines
> of
> code. I was also thinking of inverting the string (in this case, charindex
> would work since). But I'm not sure how to invert the string w/o writing
> too
> much code.
> Any help is appreciated. Thanks.

Find which SPs have quoted_identifier set wrong

Our client's application software requires all stored procedures to
have quoted_identifier set a certain way. I've tripped over this a
few times and promptly fixed it, but this morning, I had to
troubleshoot a case where someone else tripped over it. In such a
situation, how can I identify which SP(s) have it set the wrong way?

(No huge rush, the someone-else has already fixed the recent case)

SQL 2K, SP3Ed Murphy wrote:

Quote:

Originally Posted by

Our client's application software requires all stored procedures to
have quoted_identifier set a certain way. I've tripped over this a
few times and promptly fixed it, but this morning, I had to
troubleshoot a case where someone else tripped over it. In such a
situation, how can I identify which SP(s) have it set the wrong way?

You can query against the SP text as follows.

select o.[name] as 'ProcName', c.[text] as 'ProcText'
from sysobjects o
join syscomments c on o.[id] = c.[id]
where o.[name] like 'usp%'

This is based on the assumption that all your SPs start with 'usp'.
Applying a WHERE clause should enable you to filter out certain procs.

Somebody please step in and correct this as I have a feeling it's not
the best way.|||Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

Our client's application software requires all stored procedures to
have quoted_identifier set a certain way. I've tripped over this a
few times and promptly fixed it, but this morning, I had to
troubleshoot a case where someone else tripped over it. In such a
situation, how can I identify which SP(s) have it set the wrong way?

SELECT name
FROM sysobjects
WHERE objectproperty(id, 'ExecIsQuotedIdentOn') = 0

I'm assuming here that OFF is the incorrect position.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx