Thanks Bruce. I’ve never used the rollback command but it would be easier than manually “fixing” even a single row.
Claudine From: [email protected] [mailto:[email protected]] On Behalf Of Bruce Chitiea Sent: Friday, January 19, 2018 11:58 AM To: [email protected] Subject: Re[8]: [RBASE-L] - Disaster Avoidance Claudine: Excellent approach. Within my ad-hoc development environment, taking it a step farther: SET AUTOCOMMIT OFF SET TRANSACT ON <potentially destructive code> AND LIMIT = 1 [ ROLLBACK | COMMIT ] SET TRANSACT OFF SET AUTOCOMMIT ON Thanks, as ever. Bruce ------ Original Message ------ Sent: 1/15/2018 11:06:44 AM Subject: RE: Re[6]: [RBASE-L] - Disaster Avoidance: SRPL Syntax Question: From: "Claudine Robbins" <[email protected]<mailto:[email protected]>> To: "[email protected]<mailto:[email protected]>" <[email protected]<mailto:[email protected]>> Cc: Just my 2-cents worth as I have disaster experience, run command on copy of database or add AND LIMIT = 1 to see the effect of the UPDATE command on the first row before committing the whole table… Happy New Year everybody! Claudine From: [email protected]<mailto:[email protected]> [mailto:[email protected]<mailto:[email protected]>] On Behalf Of Bruce Chitiea Sent: Sunday, January 14, 2018 4:01 PM To: [email protected]<mailto:[email protected]> Subject: Re[6]: [RBASE-L] - Disaster Avoidance: SRPL Syntax Question: There's no substitute for doing things right the first time. Thanks, Buddy. Bruce ------ Original Message ------ From: "Buddy Walker" <[email protected]<mailto:[email protected]>> To: [email protected]<mailto:[email protected]> Sent: 1/14/2018 1:51:16 PM Subject: RE: Re[4]: [RBASE-L] - Disaster Avoidance: SRPL Syntax Question Bruce I would say because the “VO” should have been the column name (sourceString) so I would say it should have looked like this UPDATE transx SET trxnum = (SRPL(trxnum,’V0’,’v’,0)) WHERE trxnum LIKE ‘V0%’ Buddy From: [email protected]<mailto:[email protected]> [mailto:[email protected]<mailto:[email protected]>] On Behalf Of Bruce Chitiea Sent: Sunday, January 14, 2018 4:37 PM To: [email protected]<mailto:[email protected]> Subject: Re[4]: [RBASE-L] - Disaster Avoidance: SRPL Syntax Question Buddy: Thanks, you are absolutely right. That would have done the job in this context. Pays to review the Function list every now and again. Any thoughts, though, on why this: UPDATE transx SET trxnum = (SRPL('V0','V','v',0)) WHERE trxnum LIKE 'V0%' ... would replace every selected column value [ V0xxxxxx | v0xxxxxx ] to 'v0' ? My understanding of SRPL is that only the 'V' character value would be affected. Thanks Bruce ------ Original Message ------ From: "Buddy Walker" <[email protected]<mailto:[email protected]>> To: [email protected]<mailto:[email protected]> Sent: 1/14/2018 1:30:08 PM Subject: RE: Re[2]: [RBASE-L] - Disaster Avoidance: SRPL Syntax Question Bruce Maybe I’m missing something but you could have used the function ULC if “V” was the only alpha character in the field UPDATE tableNameHere SET columnName = (ULC(columnName)) WHERE if you need it. Buddy From: [email protected]<mailto:[email protected]> [mailto:[email protected]<mailto:[email protected]>] On Behalf Of Bruce Chitiea Sent: Sunday, January 14, 2018 4:12 PM To: [email protected]<mailto:[email protected]> Subject: Re[2]: [RBASE-L] - Disaster Avoidance: SRPL Syntax Question Karen: All my error. That's EXACTLY what I wanted to do (per the WHERE clause. ) I had run your syntax earlier, and failed. Then ran my fatal syntax without understanding WHY it failed. I reviewed the command history only after I posted. My syntax failed because I muffed the WHERE clause in that first run. Instead of [ WHERE trxnum LIKE 'V0%' ] ... I had typed [ WHERE trxnum = 'V0' ] ... a stand-alone value which does not exist in the column. But that leaves the question: "Why would it replace the entire string [ V00xxxxxx | v00xxxxxx ] with 'v0' ?" Thanks again, Karen Bruce ------ Original Message ------ From: "karentellef via RBASE-L" <[email protected]<mailto:[email protected]>> To: [email protected]<mailto:[email protected]> Sent: 1/14/2018 12:06:38 PM Subject: Re: [RBASE-L] - Disaster Avoidance: SRPL Syntax Question Maybe I'm reading your statement wrong, but no I don't think that's what you wanted to do. Did you want to do this? UPDATE transx SET trxnum = (SRPL(trxnum, 'V0', 'v0', 0)) Karen -----Original Message----- From: Bruce Chitiea <[email protected]<mailto:[email protected]>> To: rbase-l <[email protected]<mailto:[email protected]>> Sent: Sun, Jan 14, 2018 12:11 pm Subject: [RBASE-L] - Disaster Avoidance: SRPL Syntax Question Happy New Year to all! Might this be the correct SRPL syntax for replacing a substring within a text column? If not, what? SET VAR vTargetColumn TEXT = NULL SET VAR vTargetColumn = 'ColumnNAME' UPDATE TableNAME SET &vTargetColumn = (SRPL(&vTargetColumn,'SearchString','ReplaceString',0)) I ask because I attempted a SRPL on a column using a literal string in place of '&vTargetColumn', with an ugly result (Fortunately I had a backup. ) Target column 'transx' contained several thousand values [ V00xxxxxx | v00xxxxxx ] where 'x' might be any digit. For visual clarity-in-reporting's sake, I decided to replace the legacy capital 'V' with lower case 'v'. UPDATE transx SET trxnum = (SRPL('V0','V','v',0)) WHERE trxnum LIKE 'V0%' This produced a uniform value of 'v0'. No trailing digits. Disaster. Restore from backup. ??? Thanks very much, Bruce Chitiea SafeSectors, Inc. 909.238.9012 m -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]<mailto:[email protected]>. For more options, visit https://groups.google.com/d/optout. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]<mailto:[email protected]>. For more options, visit https://groups.google.com/d/optout. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]<mailto:[email protected]>. For more options, visit https://groups.google.com/d/optout. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]<mailto:[email protected]>. For more options, visit https://groups.google.com/d/optout. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]<mailto:[email protected]>. For more options, visit https://groups.google.com/d/optout. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]<mailto:[email protected]>. For more options, visit https://groups.google.com/d/optout. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]<mailto:[email protected]>. For more options, visit https://groups.google.com/d/optout. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]<mailto:[email protected]>. For more options, visit https://groups.google.com/d/optout. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]<mailto:[email protected]>. For more options, visit https://groups.google.com/d/optout. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
