Solved

String With Multiple Delimiters

Posted on 2011-09-08
14
286 Views
Last Modified: 2012-05-12
I've seen examples, but I can't get anything I see to work.  How can I extract the text between two symbols, specifically a dash symbol?
0
Comment
Question by:error_prone
  • 4
  • 4
  • 3
  • +3
14 Comments
 
LVL 1

Expert Comment

by:rerard
Comment Utility
I'm not sure what your string looks like, but if it's like this:

string str = "asdfasdfsf-something-asdfasdfdsf"

... and you want to retrieve the "something", this should do it:

string result = str.Split('-')[1]

If that doesn't do it for you, give me an example of what your string looks like and I'll figure it out.
0
 

Author Comment

by:error_prone
Comment Utility
Hmm, this is VBA right?  What about through a query?
0
 
LVL 1

Expert Comment

by:rerard
Comment Utility
sorry I just realized I'm in the wrong forum, thought it was a c# question.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
dim str as string, sResult as string

str="aaaddd-xxxxxx-zzzzzzz"

to get the xxxxxx

sResult=split(str,"-")(1)
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
error_prone,
that is in VBA
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
Comment Utility
Essentially the same as what rerard demonstrates--just with a slightly different syntax:

SELECT Split(column_name, delimiter) FROM table_name

Open in new window

0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
split does not work in a query...
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 1

Expert Comment

by:rerard
Comment Utility
If you want it in a TSQL statement you can use substring + charindex
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
you have to write a function

Function getMyString(str as string)

getMyString=split(str,"-")(1)

End function

to use in a query

select getMyString([fieldName]) from tablex
0
 
LVL 1

Expert Comment

by:rerard
Comment Utility
here is a quick and dirty example:


declare @STRING varchar(50)
set @STRING = 'blah-something-blah'
select substring(@STRING,charindex('-',@STRING)+1,charindex('-',@string,charindex('-',@string) + 1)-charindex('-',@STRING)-1)
0
 

Author Comment

by:error_prone
Comment Utility
I'm using Access.  I think I will go with capricorn's.  
0
 
LVL 4

Expert Comment

by:AgeOfEmpires
Comment Utility
Regular expressions are perfect for this sort of operation.  They are a little effort to learn, but absolutely worth learning.

http://msdn.microsoft.com/en-us/library/6wzad2b2(v=VS.85).aspx
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
Comment Utility
error_prone,

If you always need the text between the first dash and the second dash, you do not need any VBA for that.  The following returns a null if there are not at least two dashes:

SELECT SomeColumn, IIf((Len(SomeColumn) - Len(Replace(SomeColumn, "-", ""))) > 1, 
    Mid(SomeColumn, InStr(1, SomeColumn, "-") + 1, InStr(InStr(1, SomeColumn, "-") + 1, SomeColumn, "-") - 
    InStr(1, SomeColumn, "-") - 1), Null) AS Extract
FROM SomeTable

Open in new window


If you want to enable Split for use in a query, I would recommend something more like this, which will be something you can use in any instance:

Function SplitItem(Index As Long, InputStr As String, Optional Delimiter As String = " ", _
    Optional Limit As Long = -1, Optional CompareMode As VbCompareMethod = vbTextCompare)
    
    Dim arr As Variant
    Dim GetMember As Long
    
    SplitItem = ""
    
    If InputStr <> "" Then
        arr = Split(InputStr, Delimiter, Limit, CompareMode)
        If Index = 0 Then
            GetMember = UBound(arr)
        ElseIf Index > 0 Then
            GetMember = Index - 1
        Else
            GetMember = UBound(arr) + Index + 1
        End If
        If GetMember >= LBound(arr) And GetMember <= UBound(arr) Then
            SplitItem = arr(GetMember)
        End If
    End If
    
End Function

Open in new window


Your query then becomes:

SELECT SomeColumn, SplitItem(2, SomeColumn, "-") AS Extract
FROM SomeTable

Open in new window


As much as I love Regular Expressions, to me it seems like overkill for this.  If you want to learn more, please see my article:

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/A_1336-Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html

You could take the RegExpFindSubmatch function from that article; your query then becomes:

SELECT SomeColumn, RegExpFindSubmatch(SomeColumn, "(^[^-]*)(-)([^-]*)(-)", 1, 3) AS Extract
FROM SomeTable

Open in new window


Patrick
0
 

Author Closing Comment

by:error_prone
Comment Utility
Thanks!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now