String manipulation using macros

Database
Teradata Employee

String manipulation using macros

Hi

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? 

Tags (1)
2 REPLIES
Senior Supporter

Re: String manipulation using macros

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 http://developer.teradata.com/blog/madmac/2010/03/a-few-basic-scalar-string-udfs for example

Teradata Employee

Re: String manipulation using macros

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.