asked on

Extract texts after characters is found

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
Database4.laccdb

ASKER CERTIFIED SOLUTION

John Tsioumpris

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pavel Celba

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

aikimark

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

Gustav Brock

A simple expression in your query will do it:

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

Open in new window

Pavel Celba

LOL, I should read the question...

Michael Noze

ASKER

Thanks!

@pcelba ;)