Archives of the TeradataForum
Message Posted: Thu, 08 Jan 2004 @ 12:02:57 GMT
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 15 Jun 2023|