wrong results using Right

Posted on 2005-04-19
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

    If you want just everything from the right of "::", try this:

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

    Author Comment

    Perfect! Thanks

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    The steps for moving the system databases to a new location are documented in the following technical article: However sometimes after the moving process is finished, though SQL i…
    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now