Solved

String With Multiple Delimiters

Posted on 2011-09-08
14
291 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 92

Accepted Solution

by:
Patrick Matthews earned 500 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

813 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

11 Experts available now in Live!

Get 1:1 Help Now