Archives of the TeradataForum

Message Posted: Fri, 20 Feb 2004 @ 16:12:26 GMT

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

Hi,

I am trying to manipulate a string of text into separate columns using the spaces between the words as the break points. The datatype of the column is VARCHAR and the number of words in the column I am working with varies from 3 to 10.

I have written a query using case statements that functionally works, but runs out of memory (plastic steps) at about the fifth word on a dataset of only about 62000 rows. In response to that I created a table using the portion of the code that works on the first 5 words, then ran the code for the next 5 words on that table. Again, functionally this works, but I need to find something that is efficient enough that all the words are separated. Stored procedures and UDFs have been ruled out as possibilities.

This is a snipette of the approach I have taken wtih the case statements:

[(case when position(' ' in street_address) > 0
then substring(street_address from 1 for position(' ' in street_address) -1)
else null end) as first_address_word,

(case when position (' ' in street_address) > 0
then substring(street_address from position (' ' in street_address) +1)
else null end)as address_remainder1,

(case when position (' ' in address_remainder1) > 0
then substring(address_remainder1 from 1 for position(' ' in address_remainder1) -1)
else null end) as second_address_word,]

I understand why this runs out of memory (exponential build), I just am not sure what would fix the problem (I am new to Teradata SQL). Can anyone give me a clue what might work for this problem?

Thanks in advance for any help.

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