hi, i am looking to extract a string of text from within a sentence. The string consists of 6 characters or less (4 minimum) with the last three chartacters "HHS" and in 90% of cases, comes after a space. Sometimes, though the string is at the beginning of the sentence. I tried trimming the results of a mid statement using Find to locate the substring (HHS) and then subtracted 3 to get to the start of the string:
(TRIM(MID([@Provider], FIND("HHS",[@Provider])-3,6))
That worked in most cases where the full string is either 6 characters or five in length. It didn't work where the string was at the beginning of the sentence, nor if the length was only 4 characters.
so, how can you extract a string containing "HHS" as the last three characters, where the string is max 6 characters, but could be 5 or 4, that will have a space before it if its at the end of the sentence, but not if its at the start.
examples might be:
Jonnos space ABCHHS
Monkey Gone Bananas - MHHS
Hoots Boots WBHHS Man
XYZHHS Area 54 Open to all ages
Cows Horses Dogs CHHS
(TRIM(MID([@Provider], FIND("HHS",[@Provider])-3,6))
That worked in most cases where the full string is either 6 characters or five in length. It didn't work where the string was at the beginning of the sentence, nor if the length was only 4 characters.
so, how can you extract a string containing "HHS" as the last three characters, where the string is max 6 characters, but could be 5 or 4, that will have a space before it if its at the end of the sentence, but not if its at the start.
examples might be:
Jonnos space ABCHHS
Monkey Gone Bananas - MHHS
Hoots Boots WBHHS Man
XYZHHS Area 54 Open to all ages
Cows Horses Dogs CHHS