patindex function

Question

Post reply

patindex function

shuzi

Say Hey Kid

Points: 684
More actions
September 8, 2008 at 8:48 am

#197913

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

It will only return results where ',[0-9][0-9],' is in the rightmost part of the column.

Greg

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Does it work? What's it supposed to do? It doesn't return results where ',[0-9][0-9],' is in the rightmost part of the column...

SELECT note,

PATINDEX('%,[0-9][0-9],',note) AS A,

PATINDEX('%[0-9][0-9]',note) AS B,

SUBSTRING(note, PATINDEX('%[0-9][0-9]',note), 2) AS C

FROM (SELECT CAST('A101' AS VARCHAR(4)) AS note UNION ALL

SELECT 'L101' UNION ALL

SELECT 'L10' UNION ALL

SELECT '99'

) d

Results:

note A B C

---- ----------- ----------- ----

A101 0 3 01

L101 0 3 01

L10 0 2 10

99 0 1 99

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Chris Morris (9/8/2008)
Does it work? What's it supposed to do? It doesn't return results where ',[0-9][0-9],' is in the rightmost part of the column...
SELECT note,
PATINDEX('%,[0-9][0-9],',note) AS A,
PATINDEX('%[0-9][0-9]',note) AS B,
SUBSTRING(note, PATINDEX('%[0-9][0-9]',note), 2) AS C
FROM (SELECT CAST('A101' AS VARCHAR(4)) AS note UNION ALL
SELECT 'L101' UNION ALL
SELECT 'L10' UNION ALL
SELECT '99'
) d
Results:
note A B C
---- ----------- ----------- ----
A101 0 3 01
L101 0 3 01
L10 0 2 10
99 0 1 99

I can't say I've tested the code yet, but you're asking for a pattern that doesn't exist in the data ( 2 numeric digits surrounded by commas in the LAST 4 positions), so it shouldn't return anything....or, in keeping with the function's definition, return position 0 (=not found).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

create table #matt( id int identity(1,1) primary key,note varchar(30))

insert into #Matt(note)

select 'a101' union all

select 'a1,01,' union all

select 'a101' union all

select 'a1,01,'

go

SELECT note,

PATINDEX('%,[0-9][0-9],',note) AS A,

PATINDEX('%[0-9][0-9]',note) AS B,

SUBSTRING(note, PATINDEX('%[0-9][0-9]',note), 2) AS C

FROM #matt

Try that instead....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Could be significant with OP's data too:

SELECT note,

PATINDEX('%,[0-9][0-9],',note) AS A,

PATINDEX('%[0-9][0-9]',note) AS B,

SUBSTRING(note, PATINDEX('%[0-9][0-9]',note), 2) AS C

FROM (SELECT CAST('A101' AS VARCHAR(20)) AS note UNION ALL

SELECT 'L101' UNION ALL

SELECT ' ,L101,A101,01, ' UNION ALL

SELECT '99') AS d

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Hey Matt, see also

http://www.sqlservercentral.com/Forums/Topic565548-8-1.aspx?Update=1

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden