• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 430
  • Last Modified:

MS Access query to parse string into 2 records

I have a text field called "Title" with records like this:

Selexipag for pulmonary arterial hypertension
Rilotumumab (AMG-102) for Gastric tumors with high MET expression
Vicrostatin for Cancer


I would like to parse these records in 2 fields: Drug and Indication, so that the query return two columns:

Drug                                   Indication
Selexipag                            pulmonary arterial hypertension
Rilotumumab (AMG-102)   Gastric tumors with high MET expression
Vicrostatin                          Cancer
0
tomfolinsbee
Asked:
tomfolinsbee
  • 2
  • 2
1 Solution
 
Dale FyeCommented:
if the term "for" will separate the drug from the indication, then you could use something like:

SELECT Left([yourFieldname], instr([yourFieldName], "for") -1) as Drug,
             Mid([yourFieldname], instr([yourFieldName], "for") + 4) as Indication
FROM yourtable
0
 
PatHartmanCommented:
Lecture first - fields should be atomic.  They should never contain multiple attributes.

If the field is reliably constructed so that the drug and indication are ALWAYS separated by the word " for " surrounded by spaces, you can use that string as the delimiter.

SELECT Table1.Title, Left([Title],InStr([Title]," for ")-1) AS Drug, Mid([Title],InStr([texTitlet1]," for ")+5) AS indication
FROM Table1;

If there is any deviation in the formatting of the string, all bets are off.
0
 
tomfolinsbeeAuthor Commented:
Worked perfectly first time. Thank you!
0
 
Dale FyeCommented:
Guess I should have used [Title] instead of [YourFieldName]!
0
 
tomfolinsbeeAuthor Commented:
Dale, very sorry. I didn't see your post, I should have scrolled up. Small screen. Again apologies for not splitting the points. My mistake...
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now