Solved Truncate Query Results based on delimiter (1 Viewer)

theferd

Registered User.
Local time
Today, 07:15
Joined
Dec 23, 2019
Messages
42
Is it possible when generating a query to truncate the results using characters/string as a delimiter? I have a table labeled Contacts where names are listed. One of the fields is address and includes PO Box. Each address includes PO Box #####. I would like my query to return to me the addresses but I would like to truncate the return using "PO Box" as the delimiter. Is this possible without having to edit each row of my table?

Perhaps it could be done by creating a second query based on the first?
 

Isaac

Lifelong Learner
Local time
Today, 04:15
Joined
Mar 14, 2017
Messages
8,738
You could probably use a mixture of Left and Instr() to return only the portion to the left of PO Box (if I understood you right)
 

theferd

Registered User.
Local time
Today, 07:15
Joined
Dec 23, 2019
Messages
42
That seems like the right approach but how would I incorporate that into the SQL?


Code:
Select Contacts.Address
From Contacts
 

plog

Banishment Pending
Local time
Today, 06:15
Joined
May 11, 2011
Messages
11,613
Could you provide some sample data? Show us what you have in your table and what you hope to end up with
 

theferd

Registered User.
Local time
Today, 07:15
Joined
Dec 23, 2019
Messages
42
1615487340239.png

Here is a table I created using dummy data since I will not share real addresses.
What I want to do is create query that returns the nearly the same information but with the PO Box section removed. I do not wish to edit the table as there are roughly 238 entries.

1615487383789.png
 

theferd

Registered User.
Local time
Today, 07:15
Joined
Dec 23, 2019
Messages
42
Code:
SELECT Contacts.[Mail Address], Left([Mail Address],InStr(1,[Mail Address],"PO Box")) AS Expr1
FROM Contacts;

So I got it down to this point, but it still returns the first "P" In "PO Box"
1615488269849.png
 

Isaac

Lifelong Learner
Local time
Today, 04:15
Joined
Mar 14, 2017
Messages
8,738
Very good - now just subtract one after the Instr function's final closing parenthesis (but still inside the Left function's closing parenthesis)
 

Isaac

Lifelong Learner
Local time
Today, 04:15
Joined
Mar 14, 2017
Messages
8,738
Awesome! Glad you got it working
 

Users who are viewing this thread

Top Bottom