|
Archives of the TeradataForumMessage Posted: Mon, 07 Dec 2009 @ 17:17:49 GMT
Hi Paul Please find below SQL that I wrote to do the text substitution that you're asking about. There are two versions - one that allows multiple substitutions using recursive SQL and a simple, one pass at the end of my e-mail. I will just reinforce that a UDF is considered the best way of doing this but in the absence of UDF's there are other ways around it with SQL. Please read my notes on this below in the comments. I hope that this is useful to you. Cheers Jon Downs /* Recursive text substitution technique developed by Jon Downs (2009/12/07) This recursive SQL has been created (refined from something that I had knocking around that I wrote a few years ago) to take a field with text in (found in T_TEXT_FIELD_2_B_REPLACED below) and apply a number of text substitutions based of the contents T_FIND_AND_REPLACE_TEXT. There is a sequence number (SEQ) to dictate the order the substitutions are applied. In the recursive SQL itself there is a column (field) 'SUBTIMES' and this dictates the number of times that text is substituted, everything else is dynamic. The smaller the number the less levels of recursion and hence and hence the less resource. This is an extremely resource heavy way of doing text substitution and ultimately the recommendation is to use a UDF for this kind of work - this was done solely as an intellectual exercise. Note that this can be done without recursion at all if only one substitution is required. I have provided a non-recursive SQL version (one pass) of this technique at the foot of this note. Enjoy! */ /* CREATE MULTISET TABLE T_FIND_AND_REPLACE_TEXT ( FROM_VAR CHAR(30) ,TO_VAR CHAR(30) ,SEQ INT ) PRIMARY INDEX ( SEQ ) ; Table contains list of text substitutions; text from, text to & Sequence No to dictate the order the substitutions are applied in. */ DELETE T_FIND_AND_REPLACE_TEXT ; INSERT INTO T_FIND_AND_REPLACE_TEXT values ('FRED','GEORGE',1) ;INSERT INTO T_FIND_AND_REPLACE_TEXT values ('BERT','XX' ,2) ;INSERT INTO T_FIND_AND_REPLACE_TEXT values ('AA', 'GG' ,3) ; /* CREATE MULTISET TABLE T_TEXT_FIELD_2_B_REPLACED ( VAR CHAR(30) ) PRIMARY INDEX ( VAR ) ; Table contains example text to have the substitution applied to. */ INSERT INTO T_TEXT_FIELD_2_B_REPLACED values ('FRED') ;INSERT INTO T_TEXT_FIELD_2_B_REPLACED values ('BERT') ;INSERT INTO T_TEXT_FIELD_2_B_REPLACED values ('AAAAFREDAAANN') ;INSERT INTO T_TEXT_FIELD_2_B_REPLACED values ('AAAABERTAAANN') ;INSERT INTO T_TEXT_FIELD_2_B_REPLACED values ('FREDAAANN') ;INSERT INTO T_TEXT_FIELD_2_B_REPLACED values ('BERTAAANN') ;INSERT INTO T_TEXT_FIELD_2_B_REPLACED values ('AAAAFRED') ;INSERT INTO T_TEXT_FIELD_2_B_REPLACED values ('AAAABERT') ;INSERT INTO T_TEXT_FIELD_2_B_REPLACED values ('AAAA') ;INSERT INTO T_TEXT_FIELD_2_B_REPLACED values ('AAAAB') ; /* Recursive SQL to perform string substitution. Written by Jon Downs (2009/12/07) */ WITH RECURSIVE TEMP_TABLE (VAR, DEPTH, SUBROWCOUNT, SUBTIMES) AS ( SEL ROOT.VAR AS VAR ,0 AS DEPTH ,SUBROWCOUNT ,3 AS SUBTIMES FROM T_TEXT_FIELD_2_B_REPLACED ROOT CROSS JOIN ( SEL COUNT(*) AS SUBROWCOUNT FROM T_FIND_AND_REPLACE_TEXT ) DT1 UNION ALL SEL CASE WHEN POSITION(TRIM(FROM_VAR) IN INDIRECT.VAR) = 0 OR CHAR(TRIM(INDIRECT.VAR)) = 0 THEN INDIRECT.VAR ELSE SUBSTR(TRIM(INDIRECT.VAR),1,POSITION(TRIM(FROM_VAR) IN INDIRECT.VAR) - 1) ||TRIM(TO_VAR) ||SUBSTR(TRIM(INDIRECT.VAR),POSITION(TRIM(FROM_VAR) IN INDIRECT.VAR) + CHAR(TRIM(FROM_VAR)),9999) END AS VAR ,INDIRECT.DEPTH+1 AS NEWDEPTH ,SUBROWCOUNT ,SUBTIMES FROM TEMP_TABLE INDIRECT CROSS JOIN T_FIND_AND_REPLACE_TEXT T2 WHERE ((((SUBTIMES-1)+NEWDEPTH)/SUBTIMES)(INT)) = SEQ AND NEWDEPTH <= DEPTH + 1 AND NEWDEPTH <= SUBROWCOUNT*SUBTIMES ) SELECT VAR FROM TEMP_TABLE ORDER BY 1 QUALIFY RANK(DEPTH) = 1 ; /* Simple text substitution technique using non-recursive SQL. A non-recursive, one pass version. 'AA' is the text to look for and 'A' is the substitute text. VAR has the text in that is being scanned for replacement. */ SELECT VAR ,CASE WHEN POSITION(TRIM('AA') IN VAR) = 0 OR CHAR(TRIM(VAR)) = 0 THEN VAR ELSE SUBSTR(TRIM(VAR),1,POSITION(TRIM('AA') IN VAR) - 1) ||TRIM('A') ||SUBSTR(TRIM(VAR),POSITION(TRIM('AA') IN VAR) + CHAR(TRIM('AA')),9999) END AS AA ,POSITION(TRIM('AA') IN VAR) AS POSITION_FOUND_IN FROM T_TEXT_FIELD_2_B_REPLACED T1 ;
| ||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
Copyright 2016 - All Rights Reserved | ||||||||||||||||||||||||||||||||||||||||||||||||
Last Modified: 15 Jun 2023 | ||||||||||||||||||||||||||||||||||||||||||||||||