remove any spaces from field

[neemi]

can someone help me with how I would remove spaces from a field in teradata.

I know I can use trim for white spaces but I want to remove all spaces in the field

so for col1 if a record has "123 456 72" I want to return "12345672" back in my results.

Many thanks,
Neemisha

 

[ashwini19]

Hi,

As per my understanding you are loading data through flat file.In that case you can use SED commands.

But if the space position in data value is fixed then you can use following command.
trim(SUBSTRING(col1,1, INDEX (trim ( BOTH ' ' from col1),' ') ))|| trim(SUBSTRING(col1, INDEX (trim ( BOTH ' ' from col1),' ')+1,length (col1) ))

Where COL1 will have data values like '123 456' etc.

Regards,
Ashwini