Access SQL, select all but last X char of a field.

Question

Hi,

I'm looking for a MS access SQL function similar to SUBSTRING but not quite as the length of the data varies.

I have several values in a field we'll call field1

The value length varies from 2 to 5.

In all cases I would want to select the values except the very last character. So for example:

computer
browser
mouse
game
zip

Becomes:

compute
browse
mous
gam
zi

Is there such a function?

Thanks.

Answer 1

I would just:

select left(field1, len(field1) -1) from [table]

Answer 2

For an actual analog to SubString, look at the Mid() function. I have an idiosyncratic idea of what SubStr() should do (from my Paradox days), so I wrote my own SubStr() function long ago (it splits the input string into two variables based on the separator character).

Answer 3

For ACE/Jet, I would use the MID() expression. While the LEFT() expression would work fine, MID() is preferable IMO because it makes code more portable i.e. it would be much simpler to transform the ACE/Jet proprietary MID() expression into the Standard SQL SUBSTRING.

Note that you need to test the text's length to avoid a nasty error e.g. when testing the currently-accepted answer (by King Avitus using the LEFT() expression) 'as is' via OLE DB on a zero-length string I get the error, "Data provider or other service returned an E_FAIL status" which isn't very helpful :(

I suggest using ACE/Jet's IIF() expression (which, again, is easily transformed into Standard SQL's CASE expression) to test for a length zero-length string:

SELECT column_1, 
       MID(column_1, 1, IIF(LEN(column_1) = 0, 0, LEN(column_1) - 1)) 
          AS column_1_truncated
  FROM (
        SELECT DISTINCT 'ab' AS column_1 FROM Calendar
        UNION ALL
        SELECT DISTINCT 'a' FROM Calendar
        UNION ALL
        SELECT DISTINCT '' FROM Calendar
        UNION ALL
        SELECT DISTINCT NULL FROM Calendar
       ) AS MyTable;