Interior Spaces 1

[jhall01]

I have a field that is full of important data. Unfortunately it is a combination of system generated text and human generated text...so....it is full of trash!

I have a consistent pattern of dates that i need to pull out but i have spaces(whitespaces) inbetween the "-" and such. How can i easily remove the spaces in the middle of text if the length of whitespaces varies?

Here is some example text:

12, 08- 09-2002
11-13-2003
04 - 09-02
36, 10-25-2001
24,12-24-01

the number before the comma is a term in months. I have successfully removed that if a comma exists and can trim trailing and leading spaces. but I don't know how to remove spaces in the middle of the date! Any ideas?

The number of spaces in the middle of dates range from 0 to 8.

 

[jhall01]

Well, Here is a solution I have come up with but if there is an easier way then please let me know!

exampcol is the column name for this example of VARCHAR(150)
Don't know why it is 150....but then again i didn't design the table
I break it out and have to use 2 trims because of all the lovely whitespaces!!!!

TRIM(Substr(trim(exampcol), 4, 150)) beg_dte,

Pull out the month from the previous cleaned text

substr(beg_dte, 1, 2) beg_month,

Now get the days:

substr(trim(substr(beg_dte, position("-" IN beg_dte)+1, 20)),1 ,2) beg_day,

now for the year!

substr(trim(substr(beg_dte, position("-" IN beg_dte)+1, 20),position("-" in substr(beg_dte, position("-" IN beg_dte)+1, 20)), 1, 2) beg_year,

Now after all that ugliness I can concatenate everything together:

beg_month||"-"||beg_day||"-"||beg_year beginning_date

Now there has to be a much easier way to do this and to get rid of that ugly year code.

 

[dnoeth]

"Now there has to be a much easier way to do this and to get rid of that ugly year code."

There's no better way using Teradata SQL, because there's no function like REPLACE/TRANSLATE :-(

You should try to clean the data during load, it's much easier to process all those possible variations in an INMOD written in C. Or even better try to get clean data from your source system ;-)

Btw, in V2R5.1 you'll be able to write a User Defined Function to process the data.

Dieter

 

[jhall01]

Thanks deiter but unfortunately I am just a data miner and don't do the data loads. I think it is ridiculous to have this data like it is but it is beyond my control

Thanks for your response. I didn't think there was an easier way but oh well...it works right now and i have learned to love replace/translate in VBA and other languages

 

[Distraction]

I ran into the same problem and found a different solution.

I had a varchar(20) field that had different company names in them with different number of spaces.

Do a series of TRIM statements for the length of the field.

For example:
Select
trim(substr(fieldname,1,1))|
trim(substr(fieldname,2,1))|
trim(substr(fieldname,3,1))|
trim(substr(fieldname,n,1))

...where n is the length of the field.

Make sense?

 

[RogerCruse]

Hi,
I'm guessing you could reduce this SQL -
Select
trim(substr(fieldname,1,1))|
trim(substr(fieldname,2,1))|
trim(substr(fieldname,3,1))|
trim(substr(fieldname,n,1))

To this -
Select
trim(substr(fieldname,1,2))|
trim(substr(fieldname,3,2))|
trim(substr(fieldname,5,2))|
trim(substr(fieldname,n,2))

This won't be any faster of better but at least it's less code!

Roger...