Archives of the TeradataForum

Message Posted: Mon, 07 Dec 2009 @ 17:17:49 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

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
     ;

	<Prev	Next>		<<First	<Prev	Next>	Last>>