Archives of the TeradataForum

Message Posted: Thu, 08 Jan 2004 @ 12:02:57 GMT

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

Hi Mike,

No, there's no SQL function which will do this (and before anyone else says it...) When you upgrade to V2R5.1 you can do this with a UDF.

You're correct in thinking that the TRIM function only removes leading or trailing characters.

The only way that I've seen to do this is with multiple Updates or Insert/Selects (i.e. multiple passes across the data). Not nice, but I think that's all you've got right now. If you're using SQL something like:

Update < table >
  set tel_number =   ,substring(tel_number from 1 for (position(' ' in
tel_number))-1)||substring(tel_number from (position(' ' in tel_number))+1)
Where position(' ' in trim(both from tel_number)) > 0;

NOTE 1: this only removes the first space from the data

NOTE 2: the WHERE clause can be very important because (A) if this is in a BTEQ script it allows you to trap the point at which you've removed all spaces and exit the script without unnecessary requests, and (B) it means that ONLY the rows which need changing will be changed.

This is one of those rare occassions where I generally recommend NOT doing this function with SQL. If at all possible do this removal during your ETL processing. A 'C'/Cobol/PL1/Fortran etc program will typically achieve this a lot faster than using SQL.

Cheers,

Dave

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