Solved

Pulling string between two identical characters

Posted on 2014-11-24
3
99 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 58
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

630 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