Solved

PATINDEX vs. InStr function

Posted on 2014-07-30
6
957 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
  • 3
  • 3
6 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
ID: 40231495
you will need a UDF to do this in Access, is this an option?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 119

Accepted Solution

by:
Rey Obrero 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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

863 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now