Solved

PATINDEX vs. InStr function

Posted on 2014-07-30
6
1,083 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
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

705 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