Solved

Pulling string between two identical characters

Posted on 2014-11-24
3
92 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 73

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

808 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