Is there a Stuff() Function or equivalent in MS Access

[kw_uh97]

Hello Everyone

I know SQL Server has the Stuff() function. I would like to know if there's
an equivalent fuction in MSAccess. If so, can some one list it here for me
and if one does not exist can some one help me out with stripping tags from a
string.

A little more info here, the string is within a column of a table and can
contain multiple tags and they start and end a random places within the
string (column).

Thanks In Advance For Any Assistance.
kw

 

[Dorian]

Look at the 'Replace' function in Access help.
It may do some or all of what you need.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[kw_uh97]

I think replace would only replace the occurence of a specific portion string
with another string. My strings only has the opeining tag "<" and closing tag
">" characters that are constant. I can get the position of the "<" and ">"
but have no clue how to replace the string in between the two.

Hope that makes sense and was clearly stated.
kw

 

[Jack Leach]

Using the InStr() function you can store the position in variables, and then
use the Mid() function to get everything in between using the results of the
InStr for a start and stop.

After you have that string that Mid returns (which should resemble
"<stuffinhere>"), use the Replace function to make the actual replacement.

So some air code would be like this:

Function SomeName(SomeString As String) As String
Dim Ret As String
Dim iStart as Integer
Dim iEnd as Integer
Dim sTag As String

iStart = InStr(1, SomeString, "<")
iEnd = InStr(1, SomeString, ">")
sTag = Mid(SomeString, iStart, iEnd)
Ret = Replace(SomeString, sTag)

SomeName = Ret
End Function


(I don't know that those arguments are in the correct positions... check the
help files, but the concept is there)

hth

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Dale Fye]

kw,

A couple of points.
1. You might want to add a test after the iStart line to make sure the code
doesn't process any of the successive lines if the "<" is not found. Then

2. Might want to use the following to ensure you don't encounter a ">"
before the "<".

iEnd = InStr(iStart + 1, SomeString, ">")

3. As mentioned above, might want to add a test to make sure the closing
">" is found before moving on to the next step. Then

4. The mid function accepts a string, a start position, and a number of
characters, so the subsequent line should read:

sTag = Mid(SomeString, iStart, iEnd + 1 - iStart)

 

[Jeff Boyce]

For those folks here without a SQL-Server background, explaining what the
Stuff() function does would help them figure out whether Access offers an
equivalent function.

Regards

Jeff Boyce
Microsoft Access MVP

 

[kw_uh97]

Good Advise I will do so.

kw,

A couple of points.
1. You might want to add a test after the iStart line to make sure the code
doesn't process any of the successive lines if the "<" is not found. Then

2. Might want to use the following to ensure you don't encounter a ">"
before the "<".

iEnd = InStr(iStart + 1, SomeString, ">")

3. As mentioned above, might want to add a test to make sure the closing
">" is found before moving on to the next step. Then

4. The mid function accepts a string, a start position, and a number of
characters, so the subsequent line should read:

sTag = Mid(SomeString, iStart, iEnd + 1 - iStart)

 

[egerds]

http://technet.microsoft.com/en-us/library/ms188043.aspx

wouldn't this be same as vba mid? and you can just wrap it in a vba
function that can be called from queries?


air code

public Function Access_Stuff(str_string As String, lng_start As Long,
lng_End As Long, Str_Insert As String)
Dim str_result As String
str_result = str_string
Mid(str_result, lng_start, lng_End) = Str_Insert
Access_Stuff = str_result
End Function

 

[kw_uh97]

Sorry Jeff

STUFF - Delete a specified length of characters and insert
another set of characters at a specified starting point.
For example: SELECT STUFF('asdfghjkl', 3, 5, 'XYZ')
Go
here the result set is:

asXYZkl

REPLACE - Replace all occurrences of the second given string
expression in the first string expression with a third
expression.
For Example: SELECT REPLACE('Abhay', 'a', 'KKT')
Here the result set is:

KKTbhKKTy


Hope thats clear.

 

[kw_uh97]

Sorry about that:


STUFF - Delete a specified length of characters and insert
another set of characters at a specified starting point.
For example: SELECT STUFF('asdfghjkl', 3, 5, 'XYZ')
Go
here the result set is:

asXYZkl

REPLACE - Replace all occurrences of the second given string
expression in the first string expression with a third
expression.
For Example: SELECT REPLACE('Abhay', 'a', 'KKT')
Here the result set is:

KKTbhKKTy

 

[kw_uh97]

That works for a single tag but I have multiple tags in this columns. My mind
will nor wrap aroung this concept. I would think I would loop through this
string. How would I loop through this?

Thanks For Any Assistance

 

[kw_uh97]

Sorry about that:
STUFF - Delete a specified length of characters and insert
another set of characters at a specified starting point.
For example: SELECT STUFF('asdfghjkl', 3, 5, 'XYZ')
Go
here the result set is:

asXYZkl

REPLACE - Replace all occurrences of the second given string
expression in the first string expression with a third
expression.
For Example: SELECT REPLACE('Abhay', 'a', 'KKT')
Here the result set is:

KKTbhKKTy

 

[Albert D. Kallal]

That works for a single tag but I have multiple tags in this columns. My
mind
will nor wrap aroung this concept. I would think I would loop through this
string. How would I loop through this?

first, the problem here is that stuff() in sql likely not going to help you.

Assuming you have a string like:


< one > < two > < Three > < four >

If you want the 3rd value (three), to be plucked out of the above, then


dim strString as string

Dim strString As String

strString = "<one> <two> <Three> < four >"

Debug.Print Split(Split(strString, "<")(3), ">")(0)

The above will return the string "Three"

The only question you not answered is in what context you need the above
function? (code, query, report???).

Note to readers here, the stuff() function is near identical to our mid()
function WHEN you assign a value on the LEFT side

Many people don't realize that the mid() function can be on the LEFT side of
the equation.

So,

mid$(strString,2,3) = "abcdefhighkl"

The above would replace 3 chars starting at 2, for 3 chars. In sql server,
stuff is very much like the above, but after the 1st 3 chars, then the rest
of the string would be inserted.

Stuff() is of little use here for parsing out the values.

The "code" sample I gave should do the trick here.

If this needs to be done in an access query, then you have to create
function that does this for you, since split() does NOT work in an actual
query.

So, create a public function like:

Public Function getToken(vString As String, intToken As Integer) As Variant

On Error Resume Next
getToken = Split(Split(vString, "<")(intToken), ">")(0)

End Function


Then, in your query to get the 3rd token, you go:

select Compayname, getToken([collumn name], 3) as collumname from mytable


Using stuff() from sql server would not help in this case unless I miss
reading your question...

 

[kw_uh97]

Thanks for the reply as well as setting my path straight. yes I would like
this done in a query.

and my strings would be <one> some text<two>some more text<three>and more
text<four>

So I am not sure about getToken([collumn name], 3) because I would not know
the interger and plus I want to remove all the tags.

kw

That works for a single tag but I have multiple tags in this columns. My
mind
will nor wrap aroung this concept. I would think I would loop through this
string. How would I loop through this?

first, the problem here is that stuff() in sql likely not going to help you.

Assuming you have a string like:


< one > < two > < Three > < four >

If you want the 3rd value (three), to be plucked out of the above, then


dim strString as string

Dim strString As String

strString = "<one> <two> <Three> < four >"

Debug.Print Split(Split(strString, "<")(3), ">")(0)

The above will return the string "Three"

The only question you not answered is in what context you need the above
function? (code, query, report???).

Note to readers here, the stuff() function is near identical to our mid()
function WHEN you assign a value on the LEFT side

Many people don't realize that the mid() function can be on the LEFT side of
the equation.

So,

mid$(strString,2,3) = "abcdefhighkl"

The above would replace 3 chars starting at 2, for 3 chars. In sql server,
stuff is very much like the above, but after the 1st 3 chars, then the rest
of the string would be inserted.

Stuff() is of little use here for parsing out the values.

The "code" sample I gave should do the trick here.

If this needs to be done in an access query, then you have to create
function that does this for you, since split() does NOT work in an actual
query.

So, create a public function like:

Public Function getToken(vString As String, intToken As Integer) As Variant

On Error Resume Next
getToken = Split(Split(vString, "<")(intToken), ">")(0)

End Function


Then, in your query to get the 3rd token, you go:

select Compayname, getToken([collumn name], 3) as collumname from mytable


Using stuff() from sql server would not help in this case unless I miss
reading your question...

 

[David W. Fenton]

Note to readers here, the stuff() function is near identical to
our mid() function WHEN you assign a value on the LEFT side

That's the Mid() STATEMENT instead of the Mid() FUNCTION, right?

I knew it existed but have never found any situation where I needed
it.

 

[kw_uh97]

Could you elaborate?

That's the Mid() STATEMENT instead of the Mid() FUNCTION, right?

I knew it existed but have never found any situation where I needed
it.

 

[David W. Fenton]

my strings would be <one> some text<two>some more text<three>and
more text<four>

So I am not sure about getToken([collumn name], 3) because I
would not know the interger and plus I want to remove all the
tags.

Slightly more than Air Code (written in Access, tested in Access):

Public Function StripTokens(strStartChar As String, _
strEndChar As String, varInput As Variant) As Variant
Dim strTemp As String
Dim lngStart As Long
Dim lngEnd As Long
Dim strOutput As String

If Not IsNull(varInput) And Len(strStartChar) > 0 _
And Len(strEndChar) > 0 Then
strTemp = varInput
If Left(strTemp, 1) = strStartChar Then
strTemp = Mid(strTemp, InStr(strTemp, strEndChar) + 1)
End If
Do While InStr(strTemp, strStartChar) > 0
lngStart = InStr(strTemp, strStartChar)
lngEnd = InStr(strTemp, strEndChar)
strOutput = strOutput & Left(strTemp, lngStart - 1)
strTemp = Mid(strTemp, lngEnd + 1)
Loop
strOutput = strOutput & strTemp
StripTokens = strOutput
End If
End Function

Now, one problem is that your input text:

"<one> some text<two>some more text<three>and more text <four>"

....will come out without nice-looking spaces:

" some textsome more textand more text "

....and with leading and trailing spaces. The latter are easily
gotten rid of with a Trim() on the last line of the function:

StripTokens = Trim(strOutput)

But the other is much harder, but surely with that framework, you
should be able to handle it.

One solution might be the array-based approach (using Split()):

Public Function StripTokensA(strStartChar As String, _
strEndChar As String, varInput As Variant) As Variant
Dim strTemp() As String
Dim l As Long
Dim strTest As String
Dim lngEnd As Long
Dim strOutput As String

If Not IsNull(varInput) And Len(strStartChar) > 0 _
And Len(strEndChar) > 0 Then
strTemp = Split(varInput, strStartChar)
For l = 0 To UBound(strTemp()) - 1
strTest = strTemp(l)
lngEnd = InStr(strTest, strEndChar)
If lngEnd = 0 Then
strOutput = Trim(strOutput & " " & strTest)
Else
strOutput = Trim(strOutput & " " _
& Trim(Mid(strTest, lngEnd + 1)))
End If
Next l
StripTokensA = strOutput
End If
End Function

This one is pretty solid, seems to me.

I don't know how either one of them fares performance-wise, though.

 

[kw_uh97]

BEAUTIFUL!!!! I think this will work. I tried on a small result set and it
looks good. Thank you, thank all of you.

kw

my strings would be <one> some text<two>some more text<three>and
more text<four>

So I am not sure about getToken([collumn name], 3) because I
would not know the interger and plus I want to remove all the
tags.

Slightly more than Air Code (written in Access, tested in Access):

Public Function StripTokens(strStartChar As String, _
strEndChar As String, varInput As Variant) As Variant
Dim strTemp As String
Dim lngStart As Long
Dim lngEnd As Long
Dim strOutput As String

If Not IsNull(varInput) And Len(strStartChar) > 0 _
And Len(strEndChar) > 0 Then
strTemp = varInput
If Left(strTemp, 1) = strStartChar Then
strTemp = Mid(strTemp, InStr(strTemp, strEndChar) + 1)
End If
Do While InStr(strTemp, strStartChar) > 0
lngStart = InStr(strTemp, strStartChar)
lngEnd = InStr(strTemp, strEndChar)
strOutput = strOutput & Left(strTemp, lngStart - 1)
strTemp = Mid(strTemp, lngEnd + 1)
Loop
strOutput = strOutput & strTemp
StripTokens = strOutput
End If
End Function

Now, one problem is that your input text:

"<one> some text<two>some more text<three>and more text <four>"

....will come out without nice-looking spaces:

" some textsome more textand more text "

....and with leading and trailing spaces. The latter are easily
gotten rid of with a Trim() on the last line of the function:

StripTokens = Trim(strOutput)

But the other is much harder, but surely with that framework, you
should be able to handle it.

One solution might be the array-based approach (using Split()):

Public Function StripTokensA(strStartChar As String, _
strEndChar As String, varInput As Variant) As Variant
Dim strTemp() As String
Dim l As Long
Dim strTest As String
Dim lngEnd As Long
Dim strOutput As String

If Not IsNull(varInput) And Len(strStartChar) > 0 _
And Len(strEndChar) > 0 Then
strTemp = Split(varInput, strStartChar)
For l = 0 To UBound(strTemp()) - 1
strTest = strTemp(l)
lngEnd = InStr(strTest, strEndChar)
If lngEnd = 0 Then
strOutput = Trim(strOutput & " " & strTest)
Else
strOutput = Trim(strOutput & " " _
& Trim(Mid(strTest, lngEnd + 1)))
End If
Next l
StripTokensA = strOutput
End If
End Function

This one is pretty solid, seems to me.

I don't know how either one of them fares performance-wise, though.

 

[David W. Fenton]

Could you elaborate?

On what? My ignorance?!??

Read the help file. If search for Mid() in VBE help in Access,
you'll get two answers, Mid() Function and Mid() Statement, each
with its own Help article and example code.