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.
| Author |
Topic |
|
eastbay_user
Starting Member
3 Posts |
 Posted - 2015-03-26 : 17:48:41
|
| Hi..I have an update where I need to replace the first two numbers of a string with SF and remove the last number - e.g.:029929229 >> SF992922I have an update statement which works in Oracle, but I cannot get it to work in SQL Server. The statement is below:UPDATE oraowner.ems_sam SET sam_id = replace(sam_id,sam_id,'SF'||substr(sam_id, 3, length(sam_id) - 3));Any assistance would be greatly appreciated!Thank you for your help.. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-03-26 : 19:00:08
|
| DECLARE @s varchar(50) = '029929229'SELECT LEFT('SF' + SUBSTRING(@s, 3, DATALENGTH(@s)), DATALENGTH(@s)-1)Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2015-03-26 : 19:00:46
|
| [code]UPDATE oraowner.ems_sam SET sam_id='SF'+right(left(sam_id,len(sam_id)-1),len(sam_id)-3);[/code] |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2015-03-27 : 17:32:03
|
Do you need to find the first two numbers in the string or can you assume that they are the first two characters?
I would rather be the man who bought the Brooklyn Bridge than the one who sold it. -Will Rogers |
|
|
|
|
|
|
Need help updating column using REPLACE and SubStr