All Forums Database
bhanu.gorthy 3 posts Joined 06/12
06 Sep 2012
String manipulation using macros


I am very new to teradata and has the following task at my hands.

I have an table (texttable) with a column content varchar(4000). The data within this content column looks something like this.

some text goes here %starttime% and %endtime% and some text goes here. Some additional text %startdate% %starttime% can be changed to %newdate% %newtime% and some more additional text here.

%starttime% %newdate% etc are all fields from a different table (datatable).

What needs to happen is, I should be able to identify if there are any fields in this texttable.content column that doesn't exist as a column in the datatable.

Example: if the texttable.content field contains %hello%, I should be able to say that texttable.content column contains a field that is not a column in the datatable.

I am using DBC.Columns view to find out the columns in the datatable but could not get the idea of how to check if the texttable.content column has any mismatched field names.

I am only restricted to use macros for this and not stored procedures.

Can any one help please? 

ulrich 816 posts Joined 09/09
06 Sep 2012

You will have trouble to do this with standard functions like INDEX and SUBSTRING as you don't now how many %value% things are in the string.

Minimum requirement would be to have some string parsing UDFs installed on you system. 

Check for example

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

bhanu.gorthy 3 posts Joined 06/12
06 Sep 2012

Thanks Ulrich,

Thank you for the suggestion

I thought of that route but had to leave it, as I am not sure if I can do this in the client environment.


You must sign in to leave a comment.