Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2006
    Posts
    4

    Unanswered: Access/SQL String Functions

    Good Morning,

    I have been noodling over a method to achieve the results I am looking for using the SQL Query feature within Access. I will do my best to explain my problem, as well as what I have done thus far. The example I'm using below is only one column from a larger table, however, the query I am trying to execute is only against this column.

    ExampleTable

    ProjectNAME
    ------------
    IS456-Class3-Project
    ED99-ProjectPlan-ED
    h11Z-Projectplan657
    IS1001-DBProjectplan
    ABS003-Projectplan-Tech
    VAN4003-projectplan-4.3.1

    What I am trying to do is query this column and extract specfic elements from the string. For the sake of trying to be clear, let me explain what this data is. At the highest level, these are Project Names. I need to extract a substring from each field and create a view/query with two columns, which are:

    ProjectID | Project Name
    --------- | ------------

    So here are the rules (for lack of a better term)
    First, In the example table, I have 6 different records. I *only* want to run the query against strings that have "-projectplan" included. This being the case, of the 6 records, only 4 have the string "-projectplan". those are:

    ED99-ProjectPlan-ED
    h11Z-Projectplan657
    ABS003-Projectplan-Tech
    VAN4003-projectplan-4.3.1

    Second, I need to split these records into thier two columns. The split will occur at the FIRST hyphen. The first new column will have ONLY the charachters to the left of the first hyphen, but not including the hypen. The second column will have all of the data to the right of the first hyphen, but not including the first hypen. So the results would look like this


    ProjectID | Project Name
    --------- | ------------
    ED99 | ProjectPlan-ED
    h11Z | Projectplan657
    ABS003 | Projectplan-Tech  
    VAN4003 | projectplan-4.3.1


    So far, this is as far as I have been able to get:

    ---------------------
    SELECT
    instr("projectname",'-Projectplan') as hyphenPOS,
    mid("projectname",1,INSTR("name",'-Projectplan')) as ProjectID
    FROM project
    ---------------------

    which returns

    ----------------------------
    hyphenPOS | ProjectID
    ----------- | ----------
    5 | ED99-
    5 | h11Z-
    7 | ABS003-
    8 | VAN4003-
    ------------------------------------

    Just as it appears, I am able to (1) query only the records that have "-projectplan". (2) to determine the position of the first hyphen. (3) to return the projectID - However I cannot trim the trailing hyphen.

    and so far, that is all I have. So, to wrap things up, this is my question:

    I am trying to run a query against a column which returns only those fields that match the pattern "-projectplan", to the split the string into two columns, one column having the data to the left of the hypen, the other column having the data to right of the column.

    I apologize for the length of the post. I realize that as I write this, that I am making complete sense to myself, however, as you read it, it may come across as completely unorganzied and poorly worded - I'm sorry if I'm not clear, and hope someone can help me - I have pulled out what hair i have left working on this.

    Thank you!!!
    jde@thegreendoor.com
    Last edited by disturbeDEnergy; 03-24-06 at 11:06.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    untested --
    Code:
    select left(projectname
             ,instr(projectname,'-')-1) as ProjectID
         , right(projectname
             , length(projectname)
                - instr(projectname,'-')) as ProjectName
      from project
     where projectname like '*-Projectplan*'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I apologize for the length of the post.
    never, ever apologize for providing too much information. It's not possible...

    SELECT
    instr("projectname",'-Projectplan') as hyphenPOS,
    mid("projectname",1,INSTR("name",'-Projectplan')) as ProjectID
    FROM project
    You're on the right track. To extract the string, you'll want to use the Left() function (or mid if you prefer) and pass in the position of your first hyphen as an argument:

    Code:
    Left$([projectName], InStr([projectName], "-") - 1)
    Your next requirement is to only evaluate records that contain the literal string "-Projectplan". Use wildcard characters to do this.

    This is best done in the WHERE clause:

    Code:
    WHERE [projectName] LIKE "*-Projectplan*"
    The other step is to also pull in the project name. We'll apply roughly the same logic as we did to projectid, but this time we'll take everything to the RIGHT of the first hyphen. This time you're going to find the position of the first hyphen and subtract it from the total length of the name to figure out how many characters to return (starting from the right):

    Code:
    Right$([projectName], Len([projectName]) - InStr([projectName], "-"))

    Now put it all together:

    Code:
    SELECT Left$([projectName], InStr([projectName], "-") - 1) AS project_id, Right$([projectName], Len([projectName]) - InStr([projectName], "-")) As project_name
    FROM project
    WHERE [projectName] LIKE "*-Projectplan*"

    Edit: Beaten to the punch!! That's what I get for writing the great american novel in response to a basic string parsing question...
    Last edited by Teddy; 03-24-06 at 11:44.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hey, it happens

    nice to see we both came up with exactly the same result

    could you add a word or two about why you use doublequotes to delimit your character strings, and why there's a dollar sign in two of your function names but not the other two
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I use doublequotes to delimit my strings because Access is stupid and get's confused trying to deal with single quotes (like don't, can't, etc) if the string itself is delimited with a single quote.

    I use left$() and right$() as opposed to left() and right() for efficiency. Left() and Right() are non-typed functions that accept any kind of variant as an argument. This variant may or may not require conversion before the function processes the result, and it may or may not bomb out during said conversion.

    left$() and right$() on the other hand define the argument as a string and only a string. Since access now knows good and well that this argument is going to be a string, it will not go through the evaluation and conversion process.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Mar 2006
    Posts
    4
    Thank you *very* much. This is exactly what I was looking for!

    Jerrold

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Teddy
    ... get's confused trying to deal with single quotes
    um, there's no single quote in gets confused

    well yeah, you're supposed to code two of them in a row --

    Code:
    insert into foo(bar) values ('"That''s right," he said.')
    see? and if you were to actually use single quotes to delimit strings, with internal single quotes suitably escaped, you could move your app to some other database system and not have all your strings mis-parsed as identifiers (column names)



    nice explanation of typing, thanks, but how come you didn't use Len$ and Instr$ instead of Len and Instr?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by r937
    um, there's no single quote in gets confused

    well yeah, you're supposed to code two of them in a row --

    [code]you could move your app to some other database system and not have all your strings mis-parsed as identifiers (column names)
    ah, to JET or not to JET, always a fun topic.


    nice explanation of typing, thanks, but how come you didn't use Len$ and Instr$ instead of Len and Instr?
    Len() doesn't do any intrinsic conversions as far as I know. InStr() assumes the parameter given is, in fact, a string.

    There's also the part about visual basic only accepting "strings". As soon as the VBA compiler sees a single quote, it assumes you have commented out the rest of the line... unless it's encapsulated in double quotes that is...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Mar 2006
    Posts
    163
    Teddy

    Don't mean to butt in but couldn't you just use Mid$ for the second part?
    Code:
    SELECT Left$([projectName], InStr([projectName], "-") - 1) AS project_id, Mid$([projectName], InStr([projectName], "-")+1) As project_name
    FROM project
    WHERE [projectName] Like "*-Projectplan*"

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by norie
    Teddy

    Don't mean to butt in but couldn't you just use Mid$ for the second part?
    Code:
    SELECT Left$([projectName], InStr([projectName], "-") - 1) AS project_id, Mid$([projectName], InStr([projectName], "-")+1) As project_name
    FROM project
    WHERE [projectName] Like "*-Projectplan*"
    Sure, same concept. It's a matter of personal preference.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  11. #11
    Join Date
    Mar 2006
    Posts
    163
    Teddy

    My personal preference is shorter code/formulas.

    Also me and Right have some issues - all my fault admittedly.

Posting Permissions

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