Trimming Blank Inner Spaces & Data Cleansing

Database
Fan

Trimming Blank Inner Spaces & Data Cleansing

Hello,

I'm trying to find a solution to remove random spaces in a Teradata field which have been caused by human error.  The spaces are not in the same place every time so using TRIM and SUBSTRING isn't working for me. I want to take the field out as a 5 character which start with two letters and are followed by 3 numbers.  The idea is that they look like AB123.  However some of the characters have spaces between them or the first letter has a space(s) before it.

As the spaces aren't constant neither of the syntax below work.  Does anyone have any suggestions as to how I could get remove the spaces so I am left with AB123?  I have only been using Teradata SQL Assistant for a month or so now so I'm still at a basic level.

trim(substring(X from 1 for 1)) ||

trim(substring(X from 2 for 1)) ||

trim(substring(X from 3 for 1)) ||

trim(substring(X from 4 for 1)) ||

trim(substring(X from 5 for 1)) ||

replace(replace(replace(primary_address,' ','<>'),'><',''),'<>',' ') doesn't work in the version of Teradata SQL I am using.

Any suggestions?

2 REPLIES
Senior Apprentice

Re: Trimming Blank Inner Spaces & Data Cleansing

There's no REPLACE function in Teradata, but there might be oREPLACE (REPLACE was already a keyword thus it has to be renamed).

Your query replaces multiple blanks with a single blank, but i thought you want no blanks?

In that case you might also use oTRANSLATE(x, ' ', '')

Visitor

Re: Trimming Blank Inner Spaces & Data Cleansing

Dependend on your Teradata version you also can use the regex_replace function. It is available since version TD 14.00

something like:

SELECT REGEXP_REPLACE(' AA 123   ', '[\s]', '', 1, 0, 'c')

BR Armin