• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

String With Multiple Delimiters

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
error_prone
Asked:
error_prone
  • 4
  • 4
  • 3
  • +3
1 Solution
 
rerardCommented:
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
 
error_proneAuthor Commented:
Hmm, this is VBA right?  What about through a query?
0
 
rerardCommented:
sorry I just realized I'm in the wrong forum, thought it was a c# question.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Rey Obrero (Capricorn1)Commented:
dim str as string, sResult as string

str="aaaddd-xxxxxx-zzzzzzz"

to get the xxxxxx

sResult=split(str,"-")(1)
0
 
Rey Obrero (Capricorn1)Commented:
error_prone,
that is in VBA
0
 
käµfm³d 👽Commented:
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
 
Rey Obrero (Capricorn1)Commented:
split does not work in a query...
0
 
rerardCommented:
If you want it in a TSQL statement you can use substring + charindex
0
 
Rey Obrero (Capricorn1)Commented:
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
 
rerardCommented:
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
 
error_proneAuthor Commented:
I'm using Access.  I think I will go with capricorn's.  
0
 
AgeOfEmpiresCommented:
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
 
Patrick MatthewsCommented:
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
 
error_proneAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 4
  • 4
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now