June 20, 2011 at 9:05 am
Hello everyone,
I have an oracle script like below to be converted to SQL server 2008R2:
UPDATE TESTTABLE
SET NEW_DESC = SUBSTRING(SUBSTRING(DESCRIPTION,CHARINDEX(DESCRIPTION,';')+1),CHARINDEX(SUBSTRING(DESCRIPTION,CHARINDEX(DESCRIPTION,';')+1),';')+1)
;
UPDATE TESTTABLE
SET NEW_DESC = SUBSTRING(SUBSTRING(NEW_DESC,CHARINDEX(NEW_DESC,';')+1),CHARINDEX(SUBSTRING(NEW_DESC,CHARINDEX(NEW_DESC,';')+1),';')+1)
WHERE UPPER(SUBSTRING(new_desc,1,6))= 'AAAAA'
;
In this script I have to swap the arguments of the CHARINDEX FUNCTION. Foe Ex: CHARINDEX(DESCRIPTION,';') needs to be changed to CHARINDEX(';',DESCRIPTION). Also, I need to add a third argument to each SUBSTRING FUNCTION. For Ex: SUBSTRING(DESCRIPTION, 5) to SUBSTRING(DESCRIPTION, 5, LENGTH(DESCRIPTION)).
Please suggest if I can use Regular expressions to accomplish this. Please give me some guidance on this.
Thanks and Regards,
Veena
June 20, 2011 at 9:42 am
June 20, 2011 at 2:52 pm
@steve-2: This article helped a great deal. Though I am not able to search and replace complex ones like the one I was asking earlier, it helps with the simple ones.
I used the
SEARCH EXPRESSION: {to_date\(}{\'.*\'}{\)} and
REPLACE EXPRESSION: CONVERT(DATE,\2,102\3
to convert TO_DATE() FUNCTION in oracle to CONVERT() FUNCTION in SQL server.
Thank you very much 🙂
Veena.
June 20, 2011 at 3:13 pm
Good to hear. I'm not a regular expressions expert, or at least not anymore.
If you need more complex changes, I might contact the author of the article and ask if he has suggestions. He's more familiar with them than I.
June 21, 2011 at 10:18 am
Definitely let us know if you figure it out, and if you make some notes along the way, I'd be happy to help you write an article about what you've learned.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply