SQL - Capture Specific Text and Copy to New Column/row

  • Thread starter Thread starter RichW
  • Start date Start date
R

RichW

I have a table that contains an email body in a single column/row. I need to
capture specific text from the email body, then drop that text into a new
column.

An example of the text I wish to capture and which is contained in the
current table [Email Body] is:

"....REFERENCES: Thu 5/8/2008 4:46 PM...."

I created the following SQL which successfully captures those rows that have
the noted text in it:

SELECT [Email Body]
FROM MyTableName
WHERE (MyTableName.[Email Body]) Like '*REFERENCES: ??? */*/#### *:## ?M*';

Like I mentioned, the SQL noted above is succesful in capturing and
displaying only those rows that contain the string "REFERENCES: ??? */*/####
*:## ?M"

But, I wish to capture a portion of that string (??? */*/#### *:## ?M) and
store it into a new column, so that the new column only displays, for
example, Thu 5/8/2008 4:46 PM.

Please help.

Thank you,
Rich
 
Perhaps you can use the following custom VBA function to get you started.
I've just been working on this and have not fully tested it, but perhaps it
will help you get started. If you knew the exact length of the string you
could add that into the mid.

SELECT Mid([Email Body],
eInstr([Email Body],"REFERENCES: ??? */*/#### *:## ?M")+ Len("References: "),
23) As DateTimeString
FROM MyTableName
WHERE (MyTableName.[Email Body]) Like '*REFERENCES: ??? */*/#### *:## ?M*';

That will be close (if it works), but you may have some extraneous characters
at the end of the string.

The case sensitivity of LIKE is dependent on the settings of the module that
contains this function or of the database itself. The case sensitivity is set
by the Option Compare setting of the module.

Option Compare Database
Option Compare Binary
Option Compare Text

The Option Compare statement specifies the string comparison method (Binary,
Text, or Database) for a module. If a module doesn't include an Option Compare
statement, the default text comparison method is Binary.

Option Compare Database can only be used within Microsoft Access. This results
in string comparisons based on the sort order determined by the locale ID of
the database where the string comparisons occur.

If you want to guarantee a case insensitive search add this to the top of the
module
'==========================================
Option Compare Text
Option Explicit
'==========================================


Public Function eInStr(StrIn, StrFind, _
Optional Start As Long = 1) As Integer
'strIn: String to search - allows nulls,
'converts non-strings to strings if possible
'strFind: String to find in StrIn
'Start: Look for match from this point on
'Returns a number indicating where strFind is located in strIn

Dim i As Long 'Loop counter
Dim p As Long 'Value to return

If Len(StrFind & "") = 0 Or Len(StrIn & "") = 0 Then
p = 0
Else
' If Start < 1 Then Start = 1 'Handle invalid start point
For i = Start To Len(StrIn & "")
If Mid(StrIn, i) Like StrFind & "*" Then
p = i
Exit For
End If
Next i
End If

eInStr = p

End Function

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
I have a table that contains an email body in a single column/row. I need to
capture specific text from the email body, then drop that text into a new
column.

An example of the text I wish to capture and which is contained in the
current table [Email Body] is:

"....REFERENCES: Thu 5/8/2008 4:46 PM...."

I created the following SQL which successfully captures those rows that have
the noted text in it:

SELECT [Email Body]
FROM MyTableName
WHERE (MyTableName.[Email Body]) Like '*REFERENCES: ??? */*/#### *:## ?M*';

Like I mentioned, the SQL noted above is succesful in capturing and
displaying only those rows that contain the string "REFERENCES: ??? */*/####
*:## ?M"

But, I wish to capture a portion of that string (??? */*/#### *:## ?M) and
store it into a new column, so that the new column only displays, for
example, Thu 5/8/2008 4:46 PM.

Please help.

Thank you,
Rich
Click to expand...
 
John,

Thank you for having replyied to my previous post and having provided an
excellent suggestion. Unfortunately, I have tried the script you had
suggested and for some reason I am unable to get it to work.

I suspect that the "e" preceeding "Instr" should be omitted, but I still
cannot get the script to work.

Do you have any other suggestions?

Thanks again...much appreciated.

Rich

John Spencer said:
Perhaps you can use the following custom VBA function to get you started.
I've just been working on this and have not fully tested it, but perhaps it
will help you get started. If you knew the exact length of the string you
could add that into the mid.

SELECT Mid([Email Body],
eInstr([Email Body],"REFERENCES: ??? */*/#### *:## ?M")+ Len("References: "),
23) As DateTimeString
FROM MyTableName
WHERE (MyTableName.[Email Body]) Like '*REFERENCES: ??? */*/#### *:## ?M*';

That will be close (if it works), but you may have some extraneous characters
at the end of the string.

The case sensitivity of LIKE is dependent on the settings of the module that
contains this function or of the database itself. The case sensitivity is set
by the Option Compare setting of the module.

Option Compare Database
Option Compare Binary
Option Compare Text

The Option Compare statement specifies the string comparison method (Binary,
Text, or Database) for a module. If a module doesn't include an Option Compare
statement, the default text comparison method is Binary.

Option Compare Database can only be used within Microsoft Access. This results
in string comparisons based on the sort order determined by the locale ID of
the database where the string comparisons occur.

If you want to guarantee a case insensitive search add this to the top of the
module
'==========================================
Option Compare Text
Option Explicit
'==========================================


Public Function eInStr(StrIn, StrFind, _
Optional Start As Long = 1) As Integer
'strIn: String to search - allows nulls,
'converts non-strings to strings if possible
'strFind: String to find in StrIn
'Start: Look for match from this point on
'Returns a number indicating where strFind is located in strIn

Dim i As Long 'Loop counter
Dim p As Long 'Value to return

If Len(StrFind & "") = 0 Or Len(StrIn & "") = 0 Then
p = 0
Else
' If Start < 1 Then Start = 1 'Handle invalid start point
For i = Start To Len(StrIn & "")
If Mid(StrIn, i) Like StrFind & "*" Then
p = i
Exit For
End If
Next i
End If

eInStr = p

End Function

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
I have a table that contains an email body in a single column/row. I need to
capture specific text from the email body, then drop that text into a new
column.

An example of the text I wish to capture and which is contained in the
current table [Email Body] is:

"....REFERENCES: Thu 5/8/2008 4:46 PM...."

I created the following SQL which successfully captures those rows that have
the noted text in it:

SELECT [Email Body]
FROM MyTableName
WHERE (MyTableName.[Email Body]) Like '*REFERENCES: ??? */*/#### *:## ?M*';

Like I mentioned, the SQL noted above is succesful in capturing and
displaying only those rows that contain the string "REFERENCES: ??? */*/####
*:## ?M"

But, I wish to capture a portion of that string (??? */*/#### *:## ?M) and
store it into a new column, so that the new column only displays, for
example, Thu 5/8/2008 4:46 PM.

Please help.

Thank you,
Rich
Click to expand...
Click to expand...
 
Well, the E definitely should not be omitted, since the function is a
custom function that is designed to handle wild cards. InStr is a
built-in VBA function but it does NOT handle wild cards. It treats
wildcard characters as characters in the string.

Did you copy the code to a VBA module and save the module with a name
other than the name of any function? Did you then compile the code and
see if you got any errors?

"I am unable to get the script to work" is unfortunately not very
helpful in diagnosing the problem. Did you get any error messages? OR
did Access just quit? Or did you get the wrong results? OR ????



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

John,

Thank you for having replyied to my previous post and having provided an
excellent suggestion. Unfortunately, I have tried the script you had
suggested and for some reason I am unable to get it to work.

I suspect that the "e" preceeding "Instr" should be omitted, but I still
cannot get the script to work.

Do you have any other suggestions?

Thanks again...much appreciated.

Rich

John Spencer said:
Perhaps you can use the following custom VBA function to get you started.
I've just been working on this and have not fully tested it, but perhaps it
will help you get started. If you knew the exact length of the string you
could add that into the mid.

SELECT Mid([Email Body],
eInstr([Email Body],"REFERENCES: ??? */*/#### *:## ?M")+ Len("References: "),
23) As DateTimeString
FROM MyTableName
WHERE (MyTableName.[Email Body]) Like '*REFERENCES: ??? */*/#### *:## ?M*';

That will be close (if it works), but you may have some extraneous characters
at the end of the string.

The case sensitivity of LIKE is dependent on the settings of the module that
contains this function or of the database itself. The case sensitivity is set
by the Option Compare setting of the module.

Option Compare Database
Option Compare Binary
Option Compare Text

The Option Compare statement specifies the string comparison method (Binary,
Text, or Database) for a module. If a module doesn't include an Option Compare
statement, the default text comparison method is Binary.

Option Compare Database can only be used within Microsoft Access. This results
in string comparisons based on the sort order determined by the locale ID of
the database where the string comparisons occur.

If you want to guarantee a case insensitive search add this to the top of the
module
'==========================================
Option Compare Text
Option Explicit
'==========================================


Public Function eInStr(StrIn, StrFind, _
Optional Start As Long = 1) As Integer
'strIn: String to search - allows nulls,
'converts non-strings to strings if possible
'strFind: String to find in StrIn
'Start: Look for match from this point on
'Returns a number indicating where strFind is located in strIn

Dim i As Long 'Loop counter
Dim p As Long 'Value to return

If Len(StrFind & "") = 0 Or Len(StrIn & "") = 0 Then
p = 0
Else
' If Start < 1 Then Start = 1 'Handle invalid start point
For i = Start To Len(StrIn & "")
If Mid(StrIn, i) Like StrFind & "*" Then
p = i
Exit For
End If
Next i
End If

eInStr = p

End Function

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
I have a table that contains an email body in a single column/row. I need to
capture specific text from the email body, then drop that text into a new
column.

An example of the text I wish to capture and which is contained in the
current table [Email Body] is:

"....REFERENCES: Thu 5/8/2008 4:46 PM...."

I created the following SQL which successfully captures those rows that have
the noted text in it:

SELECT [Email Body]
FROM MyTableName
WHERE (MyTableName.[Email Body]) Like '*REFERENCES: ??? */*/#### *:## ?M*';

Like I mentioned, the SQL noted above is succesful in capturing and
displaying only those rows that contain the string "REFERENCES: ??? */*/####
*:## ?M"

But, I wish to capture a portion of that string (??? */*/#### *:## ?M) and
store it into a new column, so that the new column only displays, for
example, Thu 5/8/2008 4:46 PM.

Please help.

Thank you,
Rich
Click to expand...
Click to expand...
Click to expand...
 
John,

Thank you!

With the help you've given, I have been able to create a script which
captures only those rows/columns (in a table) that have the string
"references: ", and displays all subsequent text in a new table:

The script is:

SELECT
Mid(Email_Body,InStr(Email_Body,'REFERENCES')+12,Len(Email_Body)-InStr(Email_Body,'')) AS DateTimeString
FROM FSDS_OWNER_TBL_BCS_CUST_ACK_BFSLUX_WITH_DASH
WHERE (FSDS_OWNER_TBL_BCS_CUST_ACK_BFSLUX_WITH_DASH.Email_Body) Like
'*REFERENCES: ??? */*/#### *:## ?M*';

Example of what is displayed (for one row) when script is run:

"Thu 5/8/2008 4:46 PM - Walk-in blah, blah, blah....."

I would like only the date/time data captured, not the subsequent data; for
example:

"Thu 5/8/2008 4:46 PM"

Please advise of how I may capture only the date/time data.

Thanks again,
Rich




John Spencer said:
Well, the E definitely should not be omitted, since the function is a
custom function that is designed to handle wild cards. InStr is a
built-in VBA function but it does NOT handle wild cards. It treats
wildcard characters as characters in the string.

Did you copy the code to a VBA module and save the module with a name
other than the name of any function? Did you then compile the code and
see if you got any errors?

"I am unable to get the script to work" is unfortunately not very
helpful in diagnosing the problem. Did you get any error messages? OR
did Access just quit? Or did you get the wrong results? OR ????



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

John,

Thank you for having replyied to my previous post and having provided an
excellent suggestion. Unfortunately, I have tried the script you had
suggested and for some reason I am unable to get it to work.

I suspect that the "e" preceeding "Instr" should be omitted, but I still
cannot get the script to work.

Do you have any other suggestions?

Thanks again...much appreciated.

Rich

John Spencer said:
Perhaps you can use the following custom VBA function to get you started.
I've just been working on this and have not fully tested it, but perhaps it
will help you get started. If you knew the exact length of the string you
could add that into the mid.

SELECT Mid([Email Body],
eInstr([Email Body],"REFERENCES: ??? */*/#### *:## ?M")+ Len("References: "),
23) As DateTimeString
FROM MyTableName
WHERE (MyTableName.[Email Body]) Like '*REFERENCES: ??? */*/#### *:## ?M*';

That will be close (if it works), but you may have some extraneous characters
at the end of the string.

The case sensitivity of LIKE is dependent on the settings of the module that
contains this function or of the database itself. The case sensitivity is set
by the Option Compare setting of the module.

Option Compare Database
Option Compare Binary
Option Compare Text

The Option Compare statement specifies the string comparison method (Binary,
Text, or Database) for a module. If a module doesn't include an Option Compare
statement, the default text comparison method is Binary.

Option Compare Database can only be used within Microsoft Access. This results
in string comparisons based on the sort order determined by the locale ID of
the database where the string comparisons occur.

If you want to guarantee a case insensitive search add this to the top of the
module
'==========================================
Option Compare Text
Option Explicit
'==========================================


Public Function eInStr(StrIn, StrFind, _
Optional Start As Long = 1) As Integer
'strIn: String to search - allows nulls,
'converts non-strings to strings if possible
'strFind: String to find in StrIn
'Start: Look for match from this point on
'Returns a number indicating where strFind is located in strIn

Dim i As Long 'Loop counter
Dim p As Long 'Value to return

If Len(StrFind & "") = 0 Or Len(StrIn & "") = 0 Then
p = 0
Else
' If Start < 1 Then Start = 1 'Handle invalid start point
For i = Start To Len(StrIn & "")
If Mid(StrIn, i) Like StrFind & "*" Then
p = i
Exit For
End If
Next i
End If

eInStr = p

End Function

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

RichW wrote:
I have a table that contains an email body in a single column/row. I need to
capture specific text from the email body, then drop that text into a new
column.

An example of the text I wish to capture and which is contained in the
current table [Email Body] is:

"....REFERENCES: Thu 5/8/2008 4:46 PM...."

I created the following SQL which successfully captures those rows that have
the noted text in it:

SELECT [Email Body]
FROM MyTableName
WHERE (MyTableName.[Email Body]) Like '*REFERENCES: ??? */*/#### *:## ?M*';

Like I mentioned, the SQL noted above is succesful in capturing and
displaying only those rows that contain the string "REFERENCES: ??? */*/####
*:## ?M"

But, I wish to capture a portion of that string (??? */*/#### *:## ?M) and
store it into a new column, so that the new column only displays, for
example, Thu 5/8/2008 4:46 PM.

Please help.

Thank you,
Rich
Click to expand...
Click to expand...
Click to expand...
 
SELECT
Mid(Email_Body,InStr(Email_Body,'REFERENCES')+12) AS DateTimeString
FROM FSDS_OWNER_TBL_BCS_CUST_ACK_BFSLUX_WITH_DASH
WHERE (FSDS_OWNER_TBL_BCS_CUST_ACK_BFSLUX_WITH_DASH.Email_Body) Like
'*REFERENCES: ??? */*/#### *:## ?M*';


You can try the following expression. I'm not sure if it will work for
you or not. The variable length for date and time makes this chancy.


LEFT(Mid(Email_Body,InStr(Email_Body,'REFERENCES')+12),INSTR(21,Mid(Email_Body,InStr(Email_Body,'REFERENCES')+12),"
")

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

John,

Thank you!

With the help you've given, I have been able to create a script which
captures only those rows/columns (in a table) that have the string
"references: ", and displays all subsequent text in a new table:

The script is:

SELECT
Mid(Email_Body,InStr(Email_Body,'REFERENCES')+12,Len(Email_Body)-InStr(Email_Body,'')) AS DateTimeString
FROM FSDS_OWNER_TBL_BCS_CUST_ACK_BFSLUX_WITH_DASH
WHERE (FSDS_OWNER_TBL_BCS_CUST_ACK_BFSLUX_WITH_DASH.Email_Body) Like
'*REFERENCES: ??? */*/#### *:## ?M*';

Example of what is displayed (for one row) when script is run:

"Thu 5/8/2008 4:46 PM - Walk-in blah, blah, blah....."

I would like only the date/time data captured, not the subsequent data; for
example:

"Thu X5/8/2008 X4:46 PM"

Please advise of how I may capture only the date/time data.

Thanks again,
Rich




John Spencer said:
Well, the E definitely should not be omitted, since the function is a
custom function that is designed to handle wild cards. InStr is a
built-in VBA function but it does NOT handle wild cards. It treats
wildcard characters as characters in the string.

Did you copy the code to a VBA module and save the module with a name
other than the name of any function? Did you then compile the code and
see if you got any errors?

"I am unable to get the script to work" is unfortunately not very
helpful in diagnosing the problem. Did you get any error messages? OR
did Access just quit? Or did you get the wrong results? OR ????



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

John,

Thank you for having replyied to my previous post and having provided an
excellent suggestion. Unfortunately, I have tried the script you had
suggested and for some reason I am unable to get it to work.

I suspect that the "e" preceeding "Instr" should be omitted, but I still
cannot get the script to work.

Do you have any other suggestions?

Thanks again...much appreciated.

Rich

:

Perhaps you can use the following custom VBA function to get you started.
I've just been working on this and have not fully tested it, but perhaps it
will help you get started. If you knew the exact length of the string you
could add that into the mid.

SELECT Mid([Email Body],
eInstr([Email Body],"REFERENCES: ??? */*/#### *:## ?M")+ Len("References: "),
23) As DateTimeString
FROM MyTableName
WHERE (MyTableName.[Email Body]) Like '*REFERENCES: ??? */*/#### *:## ?M*';

That will be close (if it works), but you may have some extraneous characters
at the end of the string.

The case sensitivity of LIKE is dependent on the settings of the module that
contains this function or of the database itself. The case sensitivity is set
by the Option Compare setting of the module.

Option Compare Database
Option Compare Binary
Option Compare Text

The Option Compare statement specifies the string comparison method (Binary,
Text, or Database) for a module. If a module doesn't include an Option Compare
statement, the default text comparison method is Binary.

Option Compare Database can only be used within Microsoft Access. This results
in string comparisons based on the sort order determined by the locale ID of
the database where the string comparisons occur.

If you want to guarantee a case insensitive search add this to the top of the
module
'==========================================
Option Compare Text
Option Explicit
'==========================================


Public Function eInStr(StrIn, StrFind, _
Optional Start As Long = 1) As Integer
'strIn: String to search - allows nulls,
'converts non-strings to strings if possible
'strFind: String to find in StrIn
'Start: Look for match from this point on
'Returns a number indicating where strFind is located in strIn

Dim i As Long 'Loop counter
Dim p As Long 'Value to return

If Len(StrFind & "") = 0 Or Len(StrIn & "") = 0 Then
p = 0
Else
' If Start < 1 Then Start = 1 'Handle invalid start point
For i = Start To Len(StrIn & "")
If Mid(StrIn, i) Like StrFind & "*" Then
p = i
Exit For
End If
Next i
End If

eInStr = p

End Function

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

RichW wrote:
I have a table that contains an email body in a single column/row. I need to
capture specific text from the email body, then drop that text into a new
column.

An example of the text I wish to capture and which is contained in the
current table [Email Body] is:

"....REFERENCES: Thu 5/8/2008 4:46 PM...."

I created the following SQL which successfully captures those rows that have
the noted text in it:

SELECT [Email Body]
FROM MyTableName
WHERE (MyTableName.[Email Body]) Like '*REFERENCES: ??? */*/#### *:## ?M*';

Like I mentioned, the SQL noted above is succesful in capturing and
displaying only those rows that contain the string "REFERENCES: ??? */*/####
*:## ?M"

But, I wish to capture a portion of that string (??? */*/#### *:## ?M) and
store it into a new column, so that the new column only displays, for
example, Thu 5/8/2008 4:46 PM.

Please help.

Thank you,
Rich
Click to expand...
Click to expand...
Click to expand...
 
Back
Top