"NT" <NT@discussions.microsoft.com> wrote in message
news:05985BB1-C7CE-4D87-BEF5-269D022CD261@microsoft.com...
> Hi,
>
> Please help...!
> I have a Test_Name then I need to break all the texts from after the word
> 'cycle' to store into the new field as Comments. Please see example below
>
> Test_Name: 73025-003_BnDcycleRestart-01-045
>
> Here is my SQL statement:
>
> SUBSTRING(Test_Name, CHARINDEX('cycle', Test_Name) + 1, CHARINDEX('-',
> Test_Name, CHARINDEX('cycle', Test_Name)) - CHARINDEX('cycle',
> Test_Name) -
> 1) AS Comments
>
> The result in the new field Comments supposed to be 'Restart'; however,
> the
> result was 'ycleRestart'. Do I miss anything?
>
> Thank you in advance
> --
> NT

"Tom Cooper" wrote:

> CHARINDEX('cycle', Test_Name) finds the character where "cycle" BEGINS. So
> to get to the first character past "cycle", you need to add 5, not 1. Of
> course, when computing the length, since you've now added 5, rather than 1,
> you need to shorten the length by 4 more characters. So you want:
>
> Declare @Foo Table (Test_Name varchar(50))
> Insert @Foo (Test_Name) Values ('73025-003_BnDcycleRestart-01-045')
>
> Select SUBSTRING(Test_Name, CHARINDEX('cycle', Test_Name) + 5,
> CHARINDEX('-', Test_Name, CHARINDEX('cycle', Test_Name))
> - CHARINDEX('cycle', Test_Name) - 5) AS Comments
> From @Foo
>
> Tom
>
> "NT" <NT@discussions.microsoft.com> wrote in message
> news:05985BB1-C7CE-4D87-BEF5-269D022CD261@microsoft.com...
> > Hi,
> >
> > Please help...!
> > I have a Test_Name then I need to break all the texts from after the word
> > 'cycle' to store into the new field as Comments. Please see example below
> >
> > Test_Name: 73025-003_BnDcycleRestart-01-045
> >
> > Here is my SQL statement:
> >
> > SUBSTRING(Test_Name, CHARINDEX('cycle', Test_Name) + 1, CHARINDEX('-',
> > Test_Name, CHARINDEX('cycle', Test_Name)) - CHARINDEX('cycle',
> > Test_Name) -
> > 1) AS Comments
> >
> > The result in the new field Comments supposed to be 'Restart'; however,
> > the
> > result was 'ycleRestart'. Do I miss anything?
> >
> > Thank you in advance
> > --
> > NT
>
>