Solved

PATINDEX vs. InStr function

Posted on 2014-07-30
6
1,043 Views
Last Modified: 2014-08-20
Hello Folks,

I would like to have result in MS-Access using InStr function like we can have in SQL Server using PATINDEX.
As per below example, the result (0021) I would like from below string  

DECLARE @var NVarChar(1000)= 'H:\_Data\Mohit\277_State_V12_Modem\2007\Opeartion_Research_Pooling\U.S\1955_01_0021_V3PLAN_TF_00000_United_Arab_Emirates.pdf'
SELECT PATINDEX('%[_][0-9][0-9][0-9][0-9][_]%',@var)
SELECT SUBSTRING(@var,PATINDEX('%[_][0-9][0-9][0-9][0-9][_]%',@var)+1, 4)

Could you please assist on it in MS-Access using InStr function as result might be change but pattern will be same as used in PATINDEX aforesaid?

Best Regards,
Mohit Pandit
0
Comment
Question by:MohitPandit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40230027
in Access you can use
Select * from tableX
Where [FieldName] Like "*[_][0-9][0-9][0-9][0-9][_]*"

or
Select * from tableX
Where [FieldName] Like "%[_][0-9][0-9][0-9][0-9][_]%"
0
 
LVL 5

Author Comment

by:MohitPandit
ID: 40231194
I need result in SELECT statement as column. I don't want to filter.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40231495
you will need a UDF to do this in Access, is this an option?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Author Comment

by:MohitPandit
ID: 40271862
Yes, I need this SELECT statement in Access. Is it possible to identify pattern like we use to do with PATINDEX?
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 40272323
place this codes in a regular module


Function Whatever(xRef As String)
Dim x, j
If xRef & "" = "" Then Whatever = "": Exit Function
x = Split(xRef, "_")
For j = 0 To UBound(x)
    If IsNumeric(x(j)) And Len(x(j)) = 4 Then
        Whatever = x(j)
        Exit For
    End If
Next
End Function

to use, pass the string to the function

?whatever ("H:\_Data\Mohit\277_State_V12_Modem\2007\Opeartion_Research_Pooling\U.S\1955_01_0021_V3PLAN_TF_00000_United_Arab_Emirates.pdf")
0
 
LVL 5

Author Closing Comment

by:MohitPandit
ID: 40274252
thanks
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
What Is an Error? 2 27
Need age at date of document 5 19
SQL 2012 Instance Problem 3 59
Estimating my database size 7 13
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Viewers will learn how the fundamental information of how to create a table.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

733 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