Solved

String With Multiple Delimiters

Posted on 2011-09-08
14
294 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
[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
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

728 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