Split([fieldname],"\")(2)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The Split function returns a zero-based one-dimensional array of substrings.
The syntax is Split(expression, delimiter), where:

expression is the string expression contains substrings and delimiters
delimiter is a string character used to identify substring limits.

In other words, what the Split function is doing is creating a bunch of
substrings, breaking at each \. Split("C:\Ebooks\Web Design\ASP Dot NET Web
Developer's Guide.pdf", "/") will create an array with 4 elements in it.
Element 0 will be "C:", element 1 will be "Ebooks", element 2 will be "Web
Design", and element 3 will be "ASP Dot NET Web Developer's Guide.pdf". The
(2) in Split([fieldname],"\")(2) says only return the 2nd element.

I liked it but couldnt get it to work in the querry.
Exp1:Split([mybook],"\")(2)

Is there anything wrong with it..or do I need to write a function in a
module for it.? Thanks,,,,interesting topic
 
BrianPaul said:
The Split function returns a zero-based one-dimensional array of substrings.
The syntax is Split(expression, delimiter), where:

expression is the string expression contains substrings and delimiters
delimiter is a string character used to identify substring limits.

In other words, what the Split function is doing is creating a bunch of
substrings, breaking at each \. Split("C:\Ebooks\Web Design\ASP Dot NET Web
Developer's Guide.pdf", "/") will create an array with 4 elements in it.
Element 0 will be "C:", element 1 will be "Ebooks", element 2 will be "Web
Design", and element 3 will be "ASP Dot NET Web Developer's Guide.pdf". The
(2) in Split([fieldname],"\")(2) says only return the 2nd element.

I liked it but couldnt get it to work in the querry.
Exp1:Split([mybook],"\")(2)

Is there anything wrong with it..or do I need to write a function in a
module for it.? Thanks,,,,interesting topic
Click to expand...

Methinks you need a VBA module. Try this:

Public Function DoSplit(TheString As String, TheDelim As String,
TheIndex As Long) As String
DoSplit = Split(TheString, TheDelim)(TheIndex)
End Function

In your SQL call the function like this (note the placement of parameters):

SELECT DoSplit("C:\Ebooks\Web Design\ASP Dot NET Web Developer's
Guide.pdf","\",2) AS MyPath;

I also gave a solution for returning the lowest level folder (rather
than always the second level folder) in the original thread.

Yes, interesting topic! I'm somewhat ashamed to admit I spent hours
writing and refining a recursive function to do the same, not knowing of
the Split function. That was many years ago, and half the fun was
figuring out the recursive part. Here it is at some stage of development
for comparison:

Public Function GetString( _
ByVal FromString As String, _
SkipDelim As Integer, _
d As String) _
As String

' Return GetString as one delimited field past SkipDelim number of
delimiters
' (d) in FromString by iteratively cropping the left side of FromString up
' through the first delimiter.

Dim dLoc As Integer ' the location of the first delimiter in FromString
Dim i As Integer

If SkipDelim < 0 Then
GetString = ""
Else
dLoc = InStr(1, FromString, d)
If dLoc > 0 Then
If SkipDelim = 0 Then
GetString = Mid(FromString, 1, dLoc - 1)
Else
FromString = Mid(FromString, dLoc + 1)
GetString = GetString(FromString, SkipDelim - 1, d)
End If
Else
If SkipDelim = 0 Then
GetString = FromString
Else
GetString = ""
End If
End If
End If
End Function

HTH
 
What happens when you run the query? Make sure that, unlike the sample I
gave you, you've got the correct slash in your Split function.

Oh, what version of Access are you using? There was an issue with using the
Split function in queries in early versions of Access 2000. If that's what
you're using, you might want to write a function to wrap around the Split
function:

Function GetSecondElement(InputValue As String) As String

GetSecondElement = Split(InputValue, "\")(2)

End Function

You'd then put

Exp1:GetSecondElement([mybook])

in your query.

On the other hand, is it possible that mybook is null in some cases?
 
split it for real and persist it in a database.

split it into each part; and store it in a database. it's a LOT faster
than searching through the whole big text and trying to split it on
query-time


-Aaron
 
Interesting..but, for some reason the split function cannot be used in a
query....

So, you have to use a public function smartin pointed out...

I am not really sure why sql does not support that function, but it seems
that the expression service gets confused
due to those brackets hanging on the end.......
 
you CAN utilize functions like split.. on the database side.. with SQL
2005 express.. it's free and it ROCKS

www.novicksoftware.com download the free fn_tab_split is what it's
called

and then with SQL 2005 you can use the CROSS APPLY function to apply
these types of UDFs in a query
 
Back
Top