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?
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.
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.