Archives of the TeradataForum

Message Posted: Thu, 24 Apr 2008 @ 23:00:10 GMT

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

Craig:

This is going to be resource intensive because you didn't mention any other column except the varchar, so that is all I had to work with and assume that it is unique. If you have a smaller unique column in the table, you can substitute it for the two "sel vartext" in the union all that I used in mine (since you didn't supply table definition or column names, PI or anything else). Using a smaller unique column will make it less resource intensive by not using the entire huge varchar column containing your yucky data for the join.

You can create this table and run it yourself to see how it works. When you use your real table, you will probably need to add more of the concatenations and the max aggregate columns so that they equal the longest possible value you are looking for in your data (mine was 4 but allowed for 8).

     ct vartable (vartext varchar(255) not null unique);

     ins vartable ('4aaa4w4xyyz4z');
     ins vartable ('aa3aw3xyyzz3');
     ins vartable ('a2aa2wxyyzz');
     ins vartable ('aaa1wxyyzz');

     with recursive num_found (rec,loc,f,lvl) as
     (sel vartext,1,case when substr(vartext,1,1) between '0' and '9'
                       then substr(vartext,1,1) end, 1
     from vartable
     union all
     sel vartext,loc+1,case when substr(vartext,loc+1,1) between '0' and '9'
                       then substr(vartext,loc+1,1) end
          , lvl+case when f is not null then 1 else 0 end
     from vartable join num_found on rec=vartext and char(vartext) > loc )
     sel rec
         ,max(case when lvl = 1 and f is not null then f end)
         ||max(case when lvl = 2 and f is not null then f else '' end)
         ||max(case when lvl = 3 and f is not null then f else '' end)
         ||max(case when lvl = 4 and f is not null then f else '' end)
         ||max(case when lvl = 5 and f is not null then f else '' end)
         ||max(case when lvl = 6 and f is not null then f else '' end)
         ||max(case when lvl = 7 and f is not null then f else '' end)
         ||max(case when lvl = 8 and f is not null then f else '' end)
     from num_found
     where f is not null
     group by 1
     order by 1;

Hope this helps,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

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