Pulling string between two identical characters

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!
Mystical_IceAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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
sdstuberCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Commented:
>> 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.