SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Replacing part of a text value with a constant.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

VCMBoss44
Starting Member

11 Posts

Posted - 04/15/2013 :  13:04:19  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 04/15/2013 :  13:09:42  Show Profile  Visit chadmat's Homepage  Reply with Quote
Use the replace function

-Chad
Go to Top of Page

VCMBoss44
Starting Member

11 Posts

Posted - 04/15/2013 :  13:14:48  Show Profile  Reply with Quote
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
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 04/15/2013 :  13:35:22  Show Profile  Visit chadmat's Homepage  Reply with Quote
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
Go to Top of Page

VCMBoss44
Starting Member

11 Posts

Posted - 04/15/2013 :  13:49:32  Show Profile  Reply with Quote
Thanks, that helps a great deal!

-Craig
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/15/2013 :  13:51:35  Show Profile  Reply with Quote
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
Go to Top of Page

VCMBoss44
Starting Member

11 Posts

Posted - 04/15/2013 :  14:34:47  Show Profile  Reply with Quote
So I could use something like this:

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

-Craig
Go to Top of Page

VCMBoss44
Starting Member

11 Posts

Posted - 04/15/2013 :  14:38:00  Show Profile  Reply with Quote
Opps got that backwards:

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

-Craig

Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 04/15/2013 :  14:42:10  Show Profile  Visit chadmat's Homepage  Reply with Quote
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
Go to Top of Page

VCMBoss44
Starting Member

11 Posts

Posted - 04/15/2013 :  14:43:34  Show Profile  Reply with Quote
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
Go to Top of Page

VCMBoss44
Starting Member

11 Posts

Posted - 04/15/2013 :  14:46:12  Show Profile  Reply with Quote
Good idea using select first, thanks

-Craig
Go to Top of Page

VCMBoss44
Starting Member

11 Posts

Posted - 04/15/2013 :  15:30:31  Show Profile  Reply with Quote
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
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 04/15/2013 :  16:21:10  Show Profile  Visit chadmat's Homepage  Reply with Quote
Yes,

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

Something like that?

-Chad
Go to Top of Page

VCMBoss44
Starting Member

11 Posts

Posted - 04/15/2013 :  18:08:53  Show Profile  Reply with Quote
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
Go to Top of Page

VCMBoss44
Starting Member

11 Posts

Posted - 04/15/2013 :  18:11:33  Show Profile  Reply with Quote
Didn't refresh my screen to see your reply, but thanks.

-Craig
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 04/16/2013 :  00:48:22  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/16/2013 :  02:11:47  Show Profile  Reply with Quote
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
Go to Top of Page

VCMBoss44
Starting Member

11 Posts

Posted - 04/16/2013 :  02:14:25  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000