return a part of the long text field (1 Viewer)

jmt90404

Registered User.
Local time
Today, 17:39
Joined
Mar 24, 2018
Messages
29
I have a query that is used for a report. I want to have a field that pulls information from a long text field. The information that I need will always start with the word "Need", "Missing", or "Ready" and will be on its own line, normally the first line of the long text field. If this info isn't present then the return value should be null. Since this is for a report my space is very limited and I don't want the field to take up multiple lines in the report. How do I go about doing that?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:39
Joined
Oct 29, 2018
Messages
21,358
Hi. You could try using Regular Expression. Just a thought...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:39
Joined
Oct 29, 2018
Messages
21,358
I'm unfamiliar with Regular Expressions.
Check out this example. Hope it helps...

 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:39
Joined
May 21, 2018
Messages
8,463
Can you explain this better and what the result is? Can you provide some simple examples?
I interpret that to mean you could have a long message. You will have a line that starts with these words NMR. Most of the time it is the very first line, but it could be another line. But always at the beginning of a line.

So it is normally

Need text text text..............................................
more text more text
more text

Or it could be

text text text text
Missing text text text..............................................
more text more text
more text


If you find need, missing, ready at the beginning of a line then do you take all text from the point onward. Or only a single sentence?
If missing, ready, need is found within a line but not at the beginning do you do anything?
If no lines with NMR at the beginning then return NULL?

Regular expression may be a good solution, but you may be able to do this simpler depending on the rules. REGEX is super powerful, but writing patterns can be very difficult if not experienced doing it.
 

Isaac

Lifelong Learner
Local time
Today, 14:39
Joined
Mar 14, 2017
Messages
8,738
@jmt90404 see if this gets you started at all?

The main flaw is that it doesn't return you the information you want IF the place where the information is found is on the last line of text. Only if it's in, (as you suggested it is), the middle somewhere, or the first line.

It basically:
1. returns Null if the input is Null
2. returns Null if it can't find Need, Missing or Ready at all...on the start of a new line, in the middle of other lines
3. returns the individual line in question IF it starts with any of those 3 words, and is between other lines

If this doesn't work for your data you may need something with more thought put into it, this was just what I had time to demo
 

Attachments

  • test.zip
    1.3 MB · Views: 358

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 08:39
Joined
Jan 20, 2009
Messages
12,849
Regular expressions are not very efficient.

Try this which uses on engine functionality so is likely to be efficient.

Create a table with one field containing the search words as separate records.
Code:
SELECT DataTable.FieldsYouWant, Found.Word
FROM DataTable
LEFT JOIN
(
SELECT ID, SearchWords.Word
FROM DataTable, SearchWords
WHERE DataTable.LongTextField LIKE SearchWord.Word*"
) AS Found
ON Found.ID = DataTable.ID
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 08:39
Joined
Jan 20, 2009
Messages
12,849
The post above has an obvious error but the ongoing "Oops" fault on the board is preventing me from correcting it.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 08:39
Joined
Jan 20, 2009
Messages
12,849
Code:
WHERE DataTable.LongTextField LIKE SearchWord.Word & "*"
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:39
Joined
May 7, 2009
Messages
19,169
this RegExpression will extract the Sentence (the sentence must end in dot(.) or question mark(?) or exclamation(!),
beginning from "Need", "Missing" and/or "Ready" word.

see Query2.
 

Attachments

  • test.zip
    1.2 MB · Views: 475

Isaac

Lifelong Learner
Local time
Today, 14:39
Joined
Mar 14, 2017
Messages
8,738
My bad if that query is sufficient - I thought they OP needed a specific line extracted from other text, not just the word need/missing/ready
 

Users who are viewing this thread

Top Bottom