Solved

PATINDEX vs. InStr function

Posted on 2014-07-30
6
917 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
Comment Utility
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
Comment Utility
I need result in SELECT statement as column. I don't want to filter.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
you will need a UDF to do this in Access, is this an option?
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 5

Author Comment

by:MohitPandit
Comment Utility
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
Comment Utility
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
Comment Utility
thanks
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

762 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

8 Experts available now in Live!

Get 1:1 Help Now