Amanda Walshaw
asked on
changing descriptions
I have been given a field that I need to change a description from
Economy Funerals NSW Asset Purchases Cle
to
Value Funerals NSW Asset Purcashes Cle
There are over 500 entries, I need to change the word Economy to Value
what is the sql syntax to do this.
Economy Funerals NSW Asset Purchases Cle
to
Value Funerals NSW Asset Purcashes Cle
There are over 500 entries, I need to change the word Economy to Value
what is the sql syntax to do this.
where the word table needs to e the name of your table and the word fieldname needs to be the name of the field your changing
See update of Neilsr, but with building an update always try if your where-clause is correct by trying it in a select. OK here it looks as if it is very specific, but a good habbit makes you won't get surprised by an update where you forgot a crucial filter.
select * from table
WHERE fieldname = "Economy Funerals NSW Asset Purchases Cle"
select * from table
WHERE fieldname = "Economy Funerals NSW Asset Purchases Cle"
ASKER
no sorry there are over 500 of the chart of accounts all different descriptions
example of number of descriptions
Economy Funerals NSW Trade Debtors Colle
Economy Funerals NSW Plant & Equipment I
Economy Funerals NSW Asset Sale Proceeds
Economy Funerals NSW Asset Purchases Cle
Economy Funerals NSW Prov Depr - Plant &
Economy Funerals NSW Accrued Charges Inv
Economy Funerals NSW Preservation InvoCa
Economy Funerals NSW Electronic Notices
To
Value Funerals NSW Trade Debtors Colle
Value Funerals NSW Plant & Equipment I
Value Funerals NSW Asset Sale Proceeds
Value Funerals NSW Asset Purchases Cle
Value Funerals NSW Prov Depr - Plant &
Value Funerals NSW Accrued Charges Inv
Value Funerals NSW Preservation InvoCa
Value Funerals NSW Electronic Notices
example of number of descriptions
Economy Funerals NSW Trade Debtors Colle
Economy Funerals NSW Plant & Equipment I
Economy Funerals NSW Asset Sale Proceeds
Economy Funerals NSW Asset Purchases Cle
Economy Funerals NSW Prov Depr - Plant &
Economy Funerals NSW Accrued Charges Inv
Economy Funerals NSW Preservation InvoCa
Economy Funerals NSW Electronic Notices
To
Value Funerals NSW Trade Debtors Colle
Value Funerals NSW Plant & Equipment I
Value Funerals NSW Asset Sale Proceeds
Value Funerals NSW Asset Purchases Cle
Value Funerals NSW Prov Depr - Plant &
Value Funerals NSW Accrued Charges Inv
Value Funerals NSW Preservation InvoCa
Value Funerals NSW Electronic Notices
A small variation (no points please):
UPDATE YourTableName
SET YourColumnName = REPLACE(YourColumnName, 'Economy', 'Value')
WHERE fieldname LIKE 'Economy Funerals NSW%'
UPDATE YourTableName
SET YourColumnName = REPLACE(YourColumnName, 'Economy', 'Value')
WHERE fieldname LIKE 'Economy Funerals NSW%'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
UPDATE YourTableName
SET YourColumnName = 'Value' + SUNSTRING(YourColumnName, 8, LEN(YourColumnName) - 7)
WHERE fieldname LIKE 'Economy Funerals NSW%'
note sunstring is not recognizable, i think you mean substring
i
SET YourColumnName = 'Value' + SUNSTRING(YourColumnName, 8, LEN(YourColumnName) - 7)
WHERE fieldname LIKE 'Economy Funerals NSW%'
note sunstring is not recognizable, i think you mean substring
i
ASKER
worked well
SET fieldname = "Value Funerals NSW Asset Purcashes Cle"
WHERE fieldname = "Economy Funerals NSW Asset Purchases Cle"