Changing part of a string in SQL Server using REPLACE

This latest SQL Server tip comes from the Feb/March 2013 GP Reports Viewer newsletter. Have you ever needed to change part of a string in SQL Server? I have seen this come up in a variety of situations, like needing to change account names in the General Ledger or needing to show something differently when reporting. The basic code is as follows:

REPLACE(YourString, ‘text to replace’, ‘replace with text’)

Below is a practical example of how useful this can be.

Mass Modify in Dynamics GP allows you to quickly create new General Ledger accounts by copying from existing accounts. However, the names of the accounts either get created from the segment setup, which is not always optimal or correct, or the existing account names get copied. Most of the time, I use the latter option and have the account names copy from the exiting accounts. Here is an example of a list of account numbers for the Sales department (first segment 300) in the Dynamics GP sample company:

If you copy all of these accounts to create a new accounts for the Marketing department (first segment 350) choosing ‘Use Account Description From Existing Accounts’ the account numbers will be new, but the account names will still be the same:

Using REPLACE in SQL Server, you can quickly update the account names to reflect the correct department name. If you want to preview what the new account names will be, you can execute this script against your GP company database:

select b.ACTNUMST Account, a.ACTDESCR Old_Name,
REPLACE(a.ACTDESCR, 'Sales', 'Marketing') New_Name
from GL00100 a
inner join GL00105 b
on a.ACTINDX = b.ACTINDX
where a.ACTNUMBR_1 = '350'

Below are the results using my example:

If you’re ready to make the change, execute the following script against the company database:

update GL00100
set ACTDESCR = REPLACE(ACTDESCR, 'Sales', 'Marketing')
where ACTNUMBR_1 = '350'

Your new account numbers now have updated names in GP:

To see more tips and tricks for GP Reports Viewer, SQL , SSRS and Crystal Reports, take a look at the GP Reports Viewer newsletters. There are also more SQL Server coding tips on this blog.