Extract texts after characters is found

Michael Noze
Michael Noze used Ask the Experts™
Hi Experts,

I'm looking for a query to extract texts after a certain characters is found.
The criterias are:
- V1
- V2
- V3
- V7
- L1

In example:
1.       vwcnkn-.-cV3036 -> V3036
2.       EI-. EKI4 L1360 -> L1360
3.       cqwqc-cL1322 -> L1322
4.       VAUTO -> We don't take this value
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software & Systems Engineer
Function getDelimited(InputString as String)

Dim s As String
s = "V1;V2;V3;V7;L1"
Dim delimiters() As String
Dim position As Integer
Dim returnDelim As String
delimiters = Split(s, ";")
returnDelim =""
For i = LBound(delimiters) To UBound(delimiters)
    position = 0
    position = InStr(InputString, delimiters(i))
    If position > 0 Then
    returnDelim =Mid(InputString, position)
    Exit For
    End If
getDelimited = returnDelim
End Function

Open in new window

You were asking for a query, so here it is:
SELECT * FROM YourTable 
WHERE SomeColumn LIKE '%V1%' 
   OR SomeColumn LIKE '%V2%' 
   OR SomeColumn LIKE '%V3%' 
   OR SomeColumn LIKE '%V7%' 
   OR SomeColumn LIKE '%L1%' 

Open in new window

Top Expert 2014

1. Add this function to one of your modules.
2. Add this function into your query.  It will either return the string you want or an empty string.  If you need to limit your rows based on the result, you can also add this function to your WHERE clause.
Function Q_29166817(ByVal textValue)
    Static oRE As Object
    If oRE Is Nothing Then
        Set oRE = CreateObject("vbscript.regexp")
        oRE.Pattern = "(?:V1|V2|V3|V7|L1)\w*"
    End If
    If IsNull(textValue) Then
        Q_29166817 = vbNullString
        Exit Function
    End If
    If oRE.test(textValue) Then
        Q_29166817 = oRE.Execute(textValue)(0)
    End If
End Function

Open in new window

How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Most Valuable Expert 2015
Distinguished Expert 2018

A simple expression in your query will do it:

    IIf(Left(Right([LongCode],5),2) In ("V1","V2","V3","V7","L1"),Right([LongCode],5),Null) AS Code

Open in new window

LOL, I should read the question...



@pcelba ;)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial