January 6, 2011 at 8:43 am
Hey group,
I was reviewing a CURSOR created and ran across a line of code that makes no sense to me. I was hoping someone out there could explain it to me. Here is the line.
SET @startingIndex= Patindex('%[^0-9]%', @certDur)
What does PATINDEX do?
What does '%[^0-9]%' do?
Thank you 😀
January 6, 2011 at 9:12 am
SQL_Dba_WannaBe (1/6/2011)
Hey group,I was reviewing a CURSOR created and ran across a line of code that makes no sense to me. I was hoping someone out there could explain it to me. Here is the line.
SET @startingIndex= Patindex('%[^0-9]%', @certDur)
What does PATINDEX do?
What does '%[^0-9]%' do?
Thank you 😀
Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.
The "%" is a wild card - the first one means 0-n characters before (aka the beginning of the string); while the second one means 0-n characters after (aka the end of the string).
The [] annotates a set of characters.
The ^ means not in the set.
The 0-9 means the digits 0-9.
All together, it means the starting position of the first non-numeric character in the string.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 6, 2011 at 11:22 am
Perfect explanation. Thank you
January 6, 2011 at 12:10 pm
I'm glad I didn't stir up the mud in the water!
Now... do you need help in getting rid of that c.u.r.s.o.r.?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply