Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

String With Multiple Delimiters

Posted on 2011-09-08
14
Medium Priority
?
299 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
ID: 36506744
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
ID: 36506754
Hmm, this is VBA right?  What about through a query?
0
 
LVL 1

Expert Comment

by:rerard
ID: 36506755
sorry I just realized I'm in the wrong forum, thought it was a c# question.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36506766
dim str as string, sResult as string

str="aaaddd-xxxxxx-zzzzzzz"

to get the xxxxxx

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

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36506772
error_prone,
that is in VBA
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 36506775
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36506784
split does not work in a query...
0
 
LVL 1

Expert Comment

by:rerard
ID: 36506797
If you want it in a TSQL statement you can use substring + charindex
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36506800
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
ID: 36506829
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
ID: 36506842
I'm using Access.  I think I will go with capricorn's.  
0
 
LVL 4

Expert Comment

by:AgeOfEmpires
ID: 36506852
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 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 36510648
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
ID: 36570475
Thanks!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

824 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