using a query how would i add a symbol to 500,000 entries in one colum 1

[chaos18]

greetings,
i wish to add a "\" to the last four digits in one column and doing the process manually is insane since there is a little over 500,000 entries. How would the SQL look on a statement like that?


Thomas Gunter
Network Administrator

 

[Steve101]

Use an Update query; sample SQL based on your requirement is:

UPDATE tblYourTable
SET YourField = left(YourField, 5) & "\" & Right(YourField,4)

In this example, the field YourField in the table tblYourTable is updated by inserting the backslash between the 5th and sixth characters of a 9 character field, making it 10 characters.

A couple of things to look out for:

(a) Work in a copy of your database, just so you dont burn any bridges if you dont get it right first time.

(b) Add a WHERE clause if you want the update to be selective (I dont think you do in this case).

(c) Replace table / field names as appropriate.

Hope this helps,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[chaos18]

Thankyou Steve!
That was very helpful indeed and I even added WHERE into the statement to exclude the \'s i had already manually inserted...thanks again!

Thomas Gunter