Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004

    Unanswered: Query to return characters right of / ??!! having a duh! moment

    I want to run a query to return all characters right of /

    so ..... CRK-12/NVL would return NVL
    ......... GCS-HV01/XL would return XL etc

    Can't get my head round this at all, please could someone suggest a way forward.

    Many thanks.
    Fortune Global 500 Databank

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    you could use an expression in your SQL query...

    you could use the split function, but you'd have to write a user function to wrap it up, if you go down that route it may be better to use the instr function

    declare a function, put it in a code module, make sure its visibility is public
    then call the function from SQL or a form or a report

    public function SplitTextBlock(strText as string, strSeparator as string,optional ReturnElement as string ="R") as string
    'this function takes a text block and splits it based on the supplied separator
    'and returns the required element. if no ReturnElement is supplied return the right
    'the strSeparator MUST be supplied, if not the original text will be returned
    if isnull(strSeparator) or strlen(strSeparator)=0 then exit function
    SplitTextBlock=strtext 'set up our default return
    if isnull(strtext) or strlen(strText)=0 then exit function
    Dim CharFoundAt as integer
    CharFoundAt=instr(strText, strSeparator)
    if charfoundat>0 then 'only do this if the character is found
      if ReturnType = "L" then 'return the characters to the left of the symbol
        SplitTextBlock = left$(strtext,charfoundat-1)
      else 'returnt the characters to the right of the symbol
        SplitTextBlock = mid$(strtext,charfoundat+1)
    'else 'option may decide to return nothing if the character wasn't found.....
    '  splittextblock=""
    ***note code written on the fly, needs debugging and testting
    you could do the whole things as a expression within a query
    select mid$(mytextcolumn,instr(mytextcolumn,"/",)+1) as StufftoRightoftheforwardslash from mytable
    ***note code written on the fly, needs debugging and testting
    whether Id use a function or inline expression woudl depend on the requirement. the function is open, expandable, easier to debug and reusable. the expression just works. if I needed to use the same operation in more than one query, use a function, if its a used only in one query: use the expression
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Thanks for the tip, will put my little head towards trying to work it out!!

    Thanks again.
    Fortune Global 500 Databank

Posting Permissions

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