Replacing part of a text value with a constant.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Replacing part of a text value with a constant.

Author

Topic

VCMBoss44
Starting Member

11 Posts

Posted - 2013-04-15 : 13:04:19

Hi,
I have a table field that is 5 characters long. the first two characters id a product the last three id the plant location. My problem is I would like to change the last three characters to a constant for example "AAA" based on that location. So for example if I had "78TYG" would then change to "78AAA".
I thought the best way to start on this was to create new table using the WHERE IN clause to find the location(s) in question then change those values by using the TRIM function to remove last three characters and add the new characters.

I'm new to SQL so I could use some help to this problem or maybe just lead me in the right direction.

Thanks in advance.

chadmat
The Chadinator

1974 Posts

Posted - 2013-04-15 : 13:09:42

Use the replace function

-Chad

VCMBoss44
Starting Member

11 Posts

Posted - 2013-04-15 : 13:14:48

Would the replace function work if I had multiple locations to change or would I need to use multiple Replace functions for each location?

-Craig

chadmat
The Chadinator

1974 Posts

Posted - 2013-04-15 : 13:35:22

You could do it in 1 statement, using multiple nested replace statements, or you could do it with separate commands, and a single replace in each, with different parameters.

-Chad

VCMBoss44
Starting Member

11 Posts

Posted - 2013-04-15 : 13:49:32

Thanks, that helps a great deal!

-Craig

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-15 : 13:51:35

or you could just use STUFF to replace last 3 characters with your required location value and put IN caluse in where to select only records you want.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

VCMBoss44
Starting Member

11 Posts

Posted - 2013-04-15 : 14:34:47

So I could use something like this:

Update Table1 set ProductCode = REPLACE('ProductCode',SUBSTRING(ProductCode,3,3),"AAA")
WHERE Location IN ("778","454");

-Craig

VCMBoss44
Starting Member

11 Posts

Posted - 2013-04-15 : 14:38:00

Opps got that backwards:

Update Table1 set Location = REPLACE('Location',SUBSTRING(Location,3,3),"AAA")
WHERE ProductCode IN ("78","44");

-Craig

chadmat
The Chadinator

1974 Posts

Posted - 2013-04-15 : 14:42:10

If id is always a 2 digit number, and location 3 char string, you shouldn't need the substring, but it wont hurt.

Do a select before you do the update to make sure it returns what you expect, then change it to an update.

-Chad

VCMBoss44
Starting Member

11 Posts

Posted - 2013-04-15 : 14:43:34

I have added a field that has Location Code, so I had it right the first time.

Update Table1 set ProductCode = REPLACE('ProductCode',SUBSTRING(ProductCode,3,3),"AAA")
WHERE Location IN ("DEN","NYY");

-Craig

VCMBoss44
Starting Member

11 Posts

Posted - 2013-04-15 : 14:46:12

Good idea using select first, thanks

-Craig

VCMBoss44
Starting Member

11 Posts

Posted - 2013-04-15 : 15:30:31

Another question, can a IN statement refer to a table that has a list of values? That would be nice instead of updating the IN statement I can update a table.

-Craig

chadmat
The Chadinator

1974 Posts

Posted - 2013-04-15 : 16:21:10

Yes,

SELECT col1 FROM table1
WHERE col1 IN (SELECT col2 FROM table2)

Something like that?

-Chad

VCMBoss44
Starting Member

11 Posts

Posted - 2013-04-15 : 18:08:53

Answer my own question by trial and error.

Update Table1 set ProductCode = REPLACE('ProductCode',SUBSTRING('ProductCode',3,3),"AAA")
WHERE Location IN (SELECT LocationCode FROM table2);

-Craig

VCMBoss44
Starting Member

11 Posts

Posted - 2013-04-15 : 18:11:33

Didn't refresh my screen to see your reply, but thanks.

-Craig

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-16 : 00:48:22

Why you have used single quotes for ProductCode? >>(REPLACE('ProductCode',SUBSTRING('ProductCode',3,3),"AAA"))
Is this 'ProductCode' column name or literal string?

If ProductCode is column then that should be
set ProductCode = REPLACE(ProductCode,SUBSTRING(ProductCode,3,3),'AAA')

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-16 : 02:11:47

my suggestion was this


Update Table1 
set ProductCode = STUFF(ProductCode,PATINDEX('%[A-Za-z]%',ProductCode),LEN(ProductCode),'AAA')
WHERE Location IN ('778','454',..);

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

VCMBoss44
Starting Member

11 Posts

Posted - 2013-04-16 : 02:14:25

Actually I cut and pasted the wrong code to this discussion when was going through my trial and error. My final code didn't have the quotes.

-Craig

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources