Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Pulling string between two identical characters

Posted on 2014-11-24
3
Medium Priority
?
101 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 2000 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

DFW AZURE MEETUP TONIGHT FRI 6PM

We will be discussing what Azure Stack is, how does it fit into the suit of offerings that Azure has currently, and where can it fit into your organizations technology stack. We will also be discussing limitations of the platform while covering various applicable scenarios.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

721 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