Solved

Pulling string between two identical characters

Posted on 2014-11-24
3
95 Views
Last Modified: 2015-05-02
Hi,

We have a software system that allows users to enter "Production notes", which are then stored in a SQL database.

I have the view 'qryproductionnotes' set up, which displays the 'productionnotes' column, where the notes are stored.

The symbol they're using is "@", so they could, potentially, have something like the following as a record in the 'productionnotes' column:

Part will be in QC for machine inspection by mid morning.BKB 10/6/14  @ W2552-001 sample going to Lab 10/8 and will qualify 002, 003. EDS @

What I'm trying to do is pull ONLY the data between the "@" symbols, so in this case everything "W2552-001" through "EDS".

Here's the caveat:
In some cases, there will be a SINGLE "@" in the string; in the event someone used the symbol for another meaning, like "Part was sent out @ 2:00PM".  If there's just a single "@" in the string, we want to ignore it.  Same goes for if there's more than 2 "@"s in the string

Hope that makes sense, and hopefully it's not going to be TOO difficult :)

Thanks in advance!
0
Comment
Question by:Mystical_Ice
3 Comments
 
LVL 57
ID: 40463108
<<Hope that makes sense, and hopefully it's not going to be TOO difficult :)>>

 Well what if you get:

"Part was sent out @ 2:00 PM and was returned @ 4:00 pm the following day."

You need a database design change.  Notes should be getting stored in separate records.

Jim.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40463137
there's probably a more efficient way, but this works...


SELECT productionnotes,
       substring(
           productionnotes,
           charindex('@', productionnotes) + 1,
             charindex('@', productionnotes, charindex('@', productionnotes) + 1)
           - charindex('@', productionnotes)
           - 1
       )
  FROM qryproductionnotes
 WHERE datalength(productionnotes) - datalength(replace(productionnotes,'@','')) = 2


you might want do use  this for the where clause instead.

where len(productionnotes+'~') - len(replace(productionnotes+'~','@','')) = 2


datalength counts bytes which won't work for multibyte characters
len counts characters but not excludes trailing spaces, which, in your example, throws off the count because removing the last @ creates trailing spaces.

So, the work around for that is add a dummy character to the string before counting so there will never be a trailing space.
0
 
LVL 32

Expert Comment

by:awking00
ID: 40464851
>> If there's just a single "@" in the string, we want to ignore it.<<
By ignore it, do you mean don't select it or select the entire string without regard to any @ signs?
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question