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 |
|
Sambhav
Starting Member
31 Posts |
 Posted - 2007-08-14 : 11:02:54
|
I have to replace 'MED-40'instead of '30' in an incidentno column of incidents table ,the fiels is varchar(12),now the problem is it is replacing whereever it sees '30' while I want only the leftmost to be replaced for eg Let '301530' be the incidentno to be replaced if I run UPDATE incidents_mm set incidentno = replace(incidentno, '30', 'MD-40')where incidentno= '301530'query i get the ans given below 'MD-4015MD-40' and I just want the ans to be MD-401530In short i just want first 2 characters to be replaced is there any way i can do that Thanks in adv  |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2007-08-14 : 11:20:40
|
| How about thisUPDATE incidents_mm SET incidentno = CASE WHEN left(incedentno,2) = '30' THEN 'MD-40' + SUBSTRING(incedentno,3,50 ) ELSE incedentno ENDJim |
 |
|
|
Sambhav
Starting Member
31 Posts |
Posted - 2007-08-14 : 11:32:56
|
| Can you please explain me the arguments I mean how does it work ?? 1)CASE WHEN left(incedentno,2)2)SUBSTRING(incedentno,3,50 ) |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2007-08-14 : 11:43:02
|
| The best way to learn is to go to Books On Line. LEFT and SUBSTRING are string functions that operate on part of a string. CASE WHEN ... THEN ... ELSE ... END is like an "if .. then" statement. The statement basically reads"when the 2 left characters of incidentno = '30' then take the rest of the incedentno and add it to 'MD-40', else don't change anything.Hope that helps,Jim |
|
|
|
Sambhav
Starting Member
31 Posts |
Posted - 2007-08-14 : 12:15:53
|
| Thanks a lot it worked |
|
|
|
|
|
|
Replace specific character