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.
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, ' ', '')
Dependend on your Teradata version you also can use the regex_replace function. It is available since version TD 14.00
SELECT REGEXP_REPLACE(' AA 123 ', '[\s]', '', 1, 0, 'c')