Search code examples
mysqlsqlms-access

Get substring after first letter in string MS Access SQL Query

in MS Access I have a column "Test":

Test
1.1.8.5.200.484.56.1.27.85.266.6.3.332.1 Entry Test Blabla
7.55.41.6.4.77.4541.58.5654.123.2.2.123 Another entry I need to extract
Test Test New Entry

I want to display a new column, where everything before the first letter is removed, like:

FilteredColumn
Entry Test Blabla
Another entry I need to extract
Test Test New Entry

I tried using the mid() and InStr() function as followed:

Select

mid([Test], InStr([Test], '[az-AZ]')) as FilteredColumn

From TableA;

this should've returned the first occurence of a letter via the InStr() function and use that position as starting point for the mid() function.

This doesn't work unfortunately. I tried it with left() instead of mid() as well, but no success.

Solution

  • You didn't describe the error but I am guessing you were getting Invalid procedure call or argument error. What happens is this:

    • The InStr function can search for a string, not a pattern
      • So it returns 0 when it cannot find the string [az-AZ]
    • The Mid function expects a number greater than 0 and thus throws an error

    The workaround is to look for space character in the string, if it is present then extract the portion after it:

    SELECT IIf(InStr(Test, ' ') > 0, Mid(Test, InStr(Test, ' ') + 1), Test)
FROM ...