SQL: Cut & Paste to another field

I have a DB with a number of fields, but for the sake of this discussion let's say it only has three fields [Street],[City] and [Sub_City].
All of the records have data in both [Street] and [City] fields but for now [Sub_City] is empty.
In certain (not all) records the [Street] field contains the information that I would like to cut and paste into [Sub_City].
The [Street] information that I would like to remove from ALWAYS starts with "(" and ends with ")".

I need an SQL statement that can grab the text in between "(" and ")" and place it in the [Sub_City] field, and also delete all the text between and including the parentheses.

i.e.
Street = 123 main street (south boston)
City = Boston
Sub_City = [Null]

SQL=->

Street = 123 main street
City = Boston
Sub_City = south boston


Thanks,
BassKozz
basskozzAsked:
Who is Participating?
 
Steve BinkCommented:
Well, not at once, but you can run them one after the other.  Paste this sub into a module, and run it:

Public Sub DoUpdate()

CurrentDB.Execute "UPDATE MyTable SET [Sub_City] = PopulateSubCity([Street]) WHERE InStr(1, [Street], '(') > 0"
CurrentDB.Execute "UPDATE MyTable SET [Street] = Replace([Street], '(' & [Sub_City] & ')', '') WHERE Nz([Sub_City], '') <> ''

End Sub

Two things:

1) Make sure you paste the other sub I posted for you in the same module.  You'll need it for the query.
2) RUN IT ON A BACKUP FIRST!!!  If this does not do what you want, there is no way to roll it back.
0
 
Steve BinkCommented:
This code merely pulls the [Sub_City] string, not including parenthesis.  The original data is untouched:

Public Function PopulateSubCity(strField as String) As String
Dim x, y

x = InStr(1, strField, "(")
y = InStr(x + 1, strField, ")")

PopulateSubCity = Mid(strField, x + 1, (y - x) - 1)

End Function

Now you can run a query to update the [Sub_City] values:

UPDATE MyTable SET [Sub_City] = PopulateSubCity([Street]) WHERE InStr(1, [Street], "(") > 0

And finally, run a query to update the [Street] values:

UPDATE MyTable SET [Street] = Replace([Street], "(" & [Sub_City] & ")", "") WHERE Nz([Sub_City], "") <> ""
0
 
Steve BinkCommented:
As you may notice, there is no error-checking to speak of in that routine.  You will likely receive an error related to invalid use of NULL, or invalid function/parameter.  As you said yourself, this will only handle particular records...I would recommend finding a query that pulls just those records, and use that as the source for your UPDATE statements.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
basskozzAuthor Commented:
routinet,

is there anyway to run all of these at once ?

I need it to run all the records (5000+) ignoring the ones w/out parenthesis and scrapeing the ones that have them.
0
 
PreeceCommented:
This may work with some modification:

Private Sub Command3_Click()
    Dim sStreet As String
    Dim sCity As String
    Dim sSubCity As String
    Dim lStartParens As Long
    Dim lEndParens As Long
   
    ' get rs
   
   
    ' loop thru each record
    Do While Not ors.BOF And Not ors.EOF
        ' loop thru recordset and populate variables one record at a time
        'sStreet = "23 main street (south boston)"
        'sCity = "Boston"
        'sSubCity = ""
        sStreet = ors!Street
        sCity = ors!City
        sSubCity = ors!Sub_City
       
        ' get parens positions
        lStartParens = InStr(1, sStreet, "(")
        lEndParens = InStr(1, sStreet, ")")
       
        ' if start parens existing, then parse out date
        If lStartParens <> 0 Then
            sSubCity = Mid(sStreet, lStartParens + 1, Len(sStreet) - lStartParens - 1)
            sSubCity = Trim(sSubCity)
            sStreet = Left(sStreet, lStartParens - 1)
            sStreet = Trim(sStreet)
           
            ' update record in db
        End If
       
        ors.MoveNext
    Loop
   
End Sub

Preece
0
 
basskozzAuthor Commented:
I should have mentioned this before but the table name is "Master"

Preece,
I couldn't get your code to work, could you modify it to work with the table name?

Also, this might require two functions:
1. copy - to copy the data from [Street] into [Sub_City]
           - copy text in between parenthesis
2. delete - Remove the data from [Street]
             - Remove the text in between and including the parenthesis

p.s. bumpped points to MAX, this question is thougher then i thought.

Thanks,
BassKozz
0
 
PreeceCommented:
How did you modify the code?  You'll obviously need to get a recordset.   Also, check for isnull:

        sStreet = iif(isnull(ors!Street), "", ors!Street)
        sCity = iif(isnull(ors!City, ors!City)
        sSubCity = iif(isnull(ors!Sub_City, ors!Sub_City)


0
 
basskozzAuthor Commented:
not familiar with getting a record set?
Why would I need to check for isNull ?...
The only field that should be empty (null) is the [Sub_City] field, because nothing has been placed into it.
0
 
basskozzAuthor Commented:
routinet,

Here is the code I put into the modual:
'<<<START>>>
Public Function PopulateSubCity(strField As String) As String
Dim x, y

x = InStr(1, strField, "(")
y = InStr(x + 1, strField, ")")

PopulateSubCity = Mid(strField, x + 1, (y - x) - 1)

End Function

Public Sub DoUpdate()

CurrentDb.Execute "UPDATE Master SET [Sub_City] = PopulateSubCity([Street]) WHERE InStr(1, [Street], '(') > 0"
CurrentDb.Execute "UPDATE Master SET [Street] = Replace([Street], '(' & [Sub_City] & ')', '') WHERE Nz([Sub_City], '') <> ''"

End Sub

'<<<END>>>

I got the following error message after running it:
Run time error '5':
Invalid procedure call or argument


and it pointed to this line of code:
PopulateSubCity = Mid(strField, x + 1, (y - x) - 1)



Please help,
Thanks.
0
 
PreeceCommented:
>  not familiar with getting a record set?

Sure, I get recorsets all day long!  The question is, are you familiar with getting a record set?  ;-)
0
 
Steve BinkCommented:
Replace the sub with this:

Public Function PopulateSubCity(strField As String) As String
Dim x, y

x = InStr(1, strField, "(")
y = InStr(x + 1, strField, ")")

If ((x > 0) And (y > x)) Then
    PopulateSubCity = Mid(strField, x + 1, (y - x) - 1)
Else
    PopulateSubCity = ""
End If

End Function
0
 
basskozzAuthor Commented:
routinet,

it worked !!!
Thank you very much !

-BassKozz
0
 
manthaneinCommented:
please do try this  if you  have a use of it
some 4 months ago I created a database wherein I need to convert  "GT001 (STP1)"  to just "GT001"  I've done it using  query statement..  


first you need to extract  the data insde the ()
"Update partlist set Sub_City = mid(Street,instr(Street,'(') +1,instr(Street,')' - Street,'(') -2)  ) where Street like '*(*';"

this is the sql for removing the text inside the ()
"Update partlist set Street = mid(Street,1,instr(Street,'(') -1) where Street like '*(*';" \



but it's worth a try..  if it won't  work then I won't  bother  with this tread anymore
0
 
basskozzAuthor Commented:
routinet,

What would the code look like if I wanted to keep the Parentesis "()" and move everything including "()" to Sub_City ?

-BassKozz
0
 
Steve BinkCommented:
Basically, it's a matter of tweaking the values on the line marked below.  I've already changed them in this copy to reflect keeping the parenthesis:

Public Function PopulateSubCity(strField As String) As String
Dim x, y

x = InStr(1, strField, "(")
y = InStr(x + 1, strField, ")")

If ((x > 0) And (y > x)) Then
    PopulateSubCity = Mid(strField, x, (y - x) + 1)   '<--- This line
Else
    PopulateSubCity = ""
End If

End Function
0
 
basskozzAuthor Commented:
routinet,

that worked, but it didn't remove the text from the [Street] Field, it just copyied it to [Sub_City] ???

-BassKozz
0
 
Steve BinkCommented:
Right, but remember the second step from up above?

Public Sub DoUpdate()

CurrentDb.Execute "UPDATE Master SET [Sub_City] = PopulateSubCity([Street]) WHERE InStr(1, [Street], '(') > 0"
CurrentDb.Execute "UPDATE Master SET [Street] = Replace([Street], '(' & [Sub_City] & ')', '') WHERE Nz([Sub_City], '') <> ''"

End Sub


The first statement uses the custom function to populate the [Sub_City] field.  The second statement removes the now duplicate text from the [Street] field.
0
 
basskozzAuthor Commented:
routinet,
you there ?
-BassKozz
0
 
basskozzAuthor Commented:
It's still not removing the ([Sub_City]) from the [City]...
Here is what I have:
Public Function PopulateSubCity(strField As String) As String
Dim x, y

x = InStr(1, strField, "(")
y = InStr(x + 1, strField, ")")

If ((x > 0) And (y > x)) Then
    PopulateSubCity = Mid(strField, x, (y - x) + 1)   '<--- This line
Else
    PopulateSubCity = ""
End If

End Function


Public Sub DoUpdate()

CurrentDb.Execute "UPDATE tblMaster SET [Sub_City] = PopulateSubCity([Street]) WHERE InStr(1, [Street], '(') > 0"
CurrentDb.Execute "UPDATE tblMaster SET [Street] = Replace([Street], '(' & [Sub_City] & ')', '') WHERE Nz([Sub_City], '') <> ''"

End Sub


0
 
basskozzAuthor Commented:
BTW, I renamed my table from "Master" to "tblMaster"
0
 
Steve BinkCommented:
My bad...forgot to account for the parenthesis in the query.  Use this sub instead.

Public Sub DoUpdate()

CurrentDb.Execute "UPDATE tblMaster SET [Sub_City] = PopulateSubCity([Street]) WHERE InStr(1, [Street], '(') > 0"
CurrentDb.Execute "UPDATE tblMaster SET [Street] = Replace([Street], [Sub_City], '') WHERE Nz([Sub_City], '') <> ''"

End Sub
0
 
basskozzAuthor Commented:
Thanks routinet, werks perfectly !!!

-BassKOzz
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.

All Courses

From novice to tech pro — start learning today.