can anyone explain to me what does this patindex('%,[0-9][0-9],',note) means?
i saw the patindex () normally take %pattern%, with % front and end, but this
one doesn't, so what could that means? the note field is like this , "
,L101,A101,01, "

thanks

On Mon, 8 Sep 2008 07:47:01 -0700, tulip
<tulip@discussions.microsoft.com> wrote:

>can anyone explain to me what does this patindex('%,[0-9][0-9],',note) means?

To match the string must end with a comma, followed by any two digits,
and then another comma. Before that there can be anything, or
nothing.

% is of course a wildcard that matches any string, or no string at
all. [0-9] matches a single character in the range 0 to 9. The
commas require the comm character in that position.

>i saw the patindex () normally take %pattern%, with % front and end, but this
>one doesn't, so what could that means? the note field is like this , "
>,L101,A101,01, "

If the trailing blank in that string is really part of the note column
then I would not expect it to match. T0 match the patindex shown the
column has to end with a comma, not a blank.

Roy Harvey
Beacon Falls, CT

the note string is ",L101,A101,01,", the result return 11.


"Roy Harvey (SQL Server MVP)" wrote:

> On Mon, 8 Sep 2008 07:47:01 -0700, tulip
> <tulip@discussions.microsoft.com> wrote:
>
> >can anyone explain to me what does this patindex('%,[0-9][0-9],',note) means?
>
> To match the string must end with a comma, followed by any two digits,
> and then another comma. Before that there can be anything, or
> nothing.
>
> % is of course a wildcard that matches any string, or no string at
> all. [0-9] matches a single character in the range 0 to 9. The
> commas require the comm character in that position.
>
> >i saw the patindex () normally take %pattern%, with % front and end, but this
> >one doesn't, so what could that means? the note field is like this , "
> >,L101,A101,01, "
>
> If the trailing blank in that string is really part of the note column
> then I would not expect it to match. T0 match the patindex shown the
> column has to end with a comma, not a blank.
>
> Roy Harvey
> Beacon Falls, CT
>

On Mon, 8 Sep 2008 08:29:00 -0700, tulip
<tulip@discussions.microsoft.com> wrote:

> >can anyone explain to me what does this patindex('%,[0-9][0-9],',note) means?

>the note string is ",L101,A101,01,", the result return 11.

Yes, that is what it returns for me to. That is the start position of
the ',01,' at the end of the string. Note that it will always either
match the last four characters, or not match at all, so if it does
match the value returned will be LEN(note) - 3.

Roy Harvey
Beacon Falls, CT