wrong results using Right

Posted on 2005-04-19
Medium Priority
Last Modified: 2010-03-19
I've created the following store procedure:

CREATE   PROCEDURE dbo.uspSelectQueries2  @msg1 varchar(3000) output

set @msg1 = ''

Declare cSteps cursor  for

select New_Sql, Action_Name, comment  from steps_Total where step_no = 694 or step_no = 705 or step_no = 714 or step_no = 715

declare @SQL VARCHAR(500),  @QryName VARCHAR(300), @Comment VARCHAR(3000)

open cSteps
FETCH next from cSteps into @Sql, @QryName, @Comment
while @@fetch_status = 0
      set @msg1 = @msg1 +  right(@QryName, charindex('::', @QryName)  ) + ':  '  + @Comment + Char(13) + Char(10) + Char(13) + Char(10)

      fetch next from cSteps into @sql, @QryName, @Comment
close csteps
deallocate csteps

This works fine except for:  right(@QryName, charindex('::', @QryName)  ).
the first result is fine. It returns something like this:  ReturnTable1
the next result will return something like this: s::ResultsOK
What it is doing is based on the length the first time though the following results will return starting from the right to left return the same length as the first result. So if the first result is 10 then all other results will return the first 10 char from right to left. I just want everything from the right of ::

Any Ideas.....
Question by:MadIce
LVL 28

Accepted Solution

rafrancisco earned 1000 total points
ID: 13820074
If you want just everything from the right of "::", try this:

right(@QryName, charindex('::', reverse(@QryName)) - 1 )

Author Comment

ID: 13820101
Perfect! Thanks

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

840 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