cook

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:

300 accounts

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:

350 accounts

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:

preview changes

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:

new 350 names

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.

3 Responses to “Changing part of a string in SQL Server using REPLACE”

  1. This post is great. I realy like it!

    Like

Trackbacks/Pingbacks

  1. Changing part of a string in SQL Server using REPLACE | Victoria Yudin - DynamicAccounting.net - Microsoft Dynamics GP - Microsoft Dynamics Community - March 11, 2013

    [...] Victoria Yudin explains Changing part of a string in SQL Server using REPLACE [...]

    Like

  2. Changing part of a string in SQL Server using REPLACE – 3/11, Victoria Yudin | Partner Compete - March 11, 2013

    [...] Continue reading on Source Blog [...]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,530 other followers

%d bloggers like this: