Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2014
    Posts
    48

    Unanswered: Locating A Specific String Of Characters Within Another String

    Can anyone suggest a way to locate a specific string of characters within another string. The situation is this, I have a field which contains the full path and file name of a specific file. I want to extract a specific portion of this full path and I would like to do it from within a query. An example would be:

    file://localhost/D:/My%20Media/Music/Ace%20Frehley/12%20Picks/02%20Words%20Are%20Not%20Enough.mp3

    The portion of the full path that I want to extract is the Artist Folder and the Album Folder. In other words:

    /Ace%20Frehley/12%20Picks/

    I've had limited success using string functions but I've had to use a stepped approch eliminating one piece at a time. What I'm hoping for is something that will allow me to zero in on the Artist Name and extract out the segment I want. I can use the Artist Name as it will also be available.

    Can anyone offer any suggestions on how to accomplish this?

    Thanks in advance

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    Assuming a form called frmFind and a text box to put in the search term:

    in the query..
    where [field] like "*" & forms!frmFind!txtFind & "*"

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    or normalise your data so that the path is stored recursively element by element
    or if all your music is in a single parent directory node then use the mid function to strip off the common characters
    so lets assume that your file is always prepended with "file://localhost/D:/My%20Media/Music/", which if Ive counted correctly is 36 characters so the start of your denormalised data is character position 37.

    to find the end of the directory then you are lookign for the first / AFTER position 37
    mid(mycolumn,37, len(mycolumn) - (instr(37, mycolumn, "/") + 37)

    ..that should give you the directory below my music
    ..you could push that into a function in a public code module
    ..there is actually a generic example of how to do that here:-
    http://www.dbforums.com/showthread.p...18#post6624318

    Code:
    select 
      my,
      column,
      list,
      getelement(mycolumn, 7, "/") as Artist,
      getelement(mycolumn, 8, "/") as Album
    from
      mytable
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Sep 2014
    Posts
    48
    Yes, this is part of the normalization of my data. You are correct My media files are stored in common root folders such as Music, and Audiobooks. Those folders are contained in the My Media folder. That info is stored in a seperate table. The goal being to cut down carrying around all the repetitive parts over and over for every file.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    select 
      getelement(mycolumn, 4, "/") as Drive,
      getelement(mycolumn, 5, "/") as MediaDirectory,
      getelement(mycolumn, 6, "/") as MediaType,
      getelement(mycolumn, 7, "/") as Artist,
      getelement(mycolumn, 8, "/") as Album,
      getelement(mycolumn, 7, "/") as Track
    from
      mytable
    its not going to especially efficient but it will do the job PROVIDING the directory structure is as said. ie
    split by / not \
    stored as:-
    1: 'file:'
    2: '''
    3: 'localhost'
    4: 'D:'
    5: 'My%20Media'
    6: 'Music'
    7: 'Ace%20Frehley'
    8: '12%20Picks'
    9: '02%20Words%20Are%20Not%20Enough.mp3'

    or
    Code:
    select 
      getelement(mycolumn, 4, "/") & "/" & getelement(mycolumn, 5, "/") & "/" &  getelement(mycolumn, 6, "/")  & "/" as PathToFile,
      getelement(mycolumn, 7, "/") as Artist,
      getelement(mycolumn, 8, "/") as Album,
      getelement(mycolumn, 7, "/") as Track
    from
      mytable
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Sep 2014
    Posts
    48
    Yes the / is the delimiter for now. Once I've got the root folders and the file name stripped off I'll flip the delimeter around and scrub out the %20 characters. This data is in raw form and comes from an unnormalized single table. The source of the table is the iTunes XML file.

  7. #7
    Join Date
    Sep 2014
    Posts
    48
    Thank you for providing me with this code. It is very helpful and provides me with the means to seperate this file path into it's various elements in a rather nice fashion. The code basically does in one fell swoop what I was attempting to do in steps. I just couldn't get the pieces to fall into place. Once again thank you for helping me solve this issue. It is much appreciated.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    No problem, its what this place is about

    Incidentally it would be easier to tidy up you data BEFORE splitting it up.
    use the replace function

    Eg:-
    Code:
    update mytable set mycolumn = replace(replace(mycolumn,"%20", " "), "/", "\")
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Sep 2014
    Posts
    48
    Thanks for the additional assistance. I plan on having two versions of the database when I'm done. One that uses the web-based URL syntax and another that uses the file system syntax. I haven't decided if my UI will be desktop centered web centered or a hybrid of the two so I'm creating two versions of the databasee to cover by bases. Again, thanks for the assist.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    No. Having two dbs is in my books a 'kin stupid idea. You can reformat the data as required by using replace. It doesnt matter if you store the data as a web centric url or a path, but dont do both. Have a function that does the formatting and use that in a query.

    If someone is using Access then they are in the Microsoft world and use \ to delimit directory boundaries. Its typical of Apple to use / on a piece of software on a windows system
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Sep 2014
    Posts
    48
    I appreciate your opinion. Thanks again.

Posting Permissions

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