Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2013
    Posts
    21

    Unanswered: Select based on character position within string

    Hello,

    I am trying to write a query that creates a popup input box based on the 6th and 7th character within a string.

    Field is StationID, example = (2012080100205). This field is a concatenation of year (4 digits), ship code (2 digits), cruise number (2 digits), station number (3 digits) and event number (2 digits).

    I would like to create a query where people can search for data based on the ship code (characters 6 and 7) within this field.

    Is this possible?

    I've tried variations on the Left, Right, Mid functions, i.e.;

    Left([StationID],7) = '8', just to select based on these positions, But no luck so far.

    Any help would be greatly appreciated!

    Thank you!
    Jeanette

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You must use the Mid() function in this case:
    Code:
    SELECT... FROM... WHERE Mid(StationID, 6, 2) = '85'
    You could also use the Like operator:
    Code:
    SELECT... FROM... WHERE Mid(StationID, 6) Like '8*'
    In the first case, you extract 2 characters, beginning at the sixth position. In the second, you extract from the sixth position to the end of the string.

    Note: This kind of query can be slow if the number of rows is important because each row must be processed and no index can be used to evaluate the criteria.
    Have a nice day!

  3. #3
    Join Date
    Feb 2013
    Posts
    21
    Sweet, got it.. Thanks Sinndho!!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •