Convert a Field

Tools
Enthusiast

Convert a Field

Hello,

I have a CHAR(12) field in a table.  This field is a last 12 month field, so it provides 12 characters representing each of the past 12 month.  Problem is, the 12 characters in this field could be blank.  So when I go to export, it gets all messed up.  Is there a way to look into a field and convert any blanks into a value; say; x ?  

Field will look like this:

'1   12   1 1'

'         11 '

I want to see this:

'1xxx12xxx1x1'

'xxxxxxxxx11x'

3 REPLIES
Enthusiast

Re: Convert a Field

1. This can be achieved by OREPLACE function in TD, the equivalent of the Oracle funtion.

 Query : select oreplace(colname,' ','x') from Table; -- (' ' is space)

 Output:

1xxx12xxx1x1

xxxxxxxxx11x

However, if OREPLACE is not accessible in your TD environment, we would need to use substr functions.

2. Alternative solution, without using OREPLACE function.

Query :

  select case when substr(eid,1,1)=' ' then 'x' else substr(eid,1,1) end ||

               case          when substr(eid,2,1)=' ' then 'x' else substr(eid,2,1) end||

               case          when substr(eid,3,1)=' ' then 'x' else substr(eid,3,1) end ||

               case          when substr(eid,4,1)=' ' then 'x' else substr(eid,4,1) end ||

               case          when substr(eid,5,1)=' ' then 'x' else substr(eid,5,1) end ||

               case          when substr(eid,6,1)=' ' then 'x' else substr(eid,6,1) end ||

               case          when substr(eid,7,1)=' ' then 'x' else substr(eid,7,1) end ||

               case          when substr(eid,8,1)=' ' then 'x' else substr(eid,8,1) end ||

               case          when substr(eid,9,1)=' ' then 'x' else substr(eid,9,1) end ||

               case          when substr(eid,10,1)=' ' then 'x' else substr(eid,10,1) end ||

               case          when substr(eid,11,1)=' ' then 'x' else substr(eid,11,1) end ||

               case          when substr(eid,12,1)=' ' then 'x' else substr(eid,12,1) end 

                                as replacedstr             from Table;

Output :

1xxx12xxx1x1

xxxxxxxxx11x


Regards,

Srivignesh KN

Enthusiast

Re: Convert a Field

Thank you,

This function actually only supplies the output of 'x' up to the last real digit.  It's no problem, but the out put is more like:

'xxxxx1'

'1xx12xx1'

Rather than:

'xxxxxx1xxxxx'

'1xx12xx1xxxx'

Any idea?

Senior Apprentice

Re: Convert a Field

What's your Teradata release?

This works fine for me, but you better use oTranslate instead of oReplace:

SELECT CAST(OTRANSLATE('1   12      ', ' ', 'x') AS CHAR(12))

oReplace is built-in since TD14, if you're on an older release or got a UDF with the same name this might work differently.