Need help on Converting from Characters into Integer

Database
MBR
Enthusiast

Need help on Converting from Characters into Integer

Hi All,

below is the scenario

source has Char datatype and the data as below

COL1

------

ABCD

ACD

BDE

i want to convert COL1 data into integer like following

COL1

------

1

2

3

Can any one suggest how to do this.

Many Thanks

Balaji

7 REPLIES
Enthusiast

Re: Need help on Converting from Characters into Integer

What is the logic behind that you conclude that ABCD is 1, ACD is 2 and BDE is 3 and so on and so forth? Or is it that you want to provide a row number for each( where you can solve by row_number).....? If so  too, what happens if you have duplicates.

So questions such as these arise....

MBR
Enthusiast

Re: Need help on Converting from Characters into Integer

Thanq Raja for your reply..

yes Raja we nee to provide row number for characters. below is the info for your better understanding

Source: below field has char datatype in source which is accepting both chars and numbers

Cust_id (Char datatype)

123

235

258

abc

def

Target: below field has Char datatype in Target but casted to Int because of integer values.

Cust_id (Char datatype)

123

123

258

issue:

while joinging these two columns some data is missing,

for this if any characters coming from source we have to convert into numbers(i.e it can be any number)

Enthusiast

Re: Need help on Converting from Characters into Integer

I dont get it when you have 123 twice in target. 

I dont know if  I understand it correctly. It looks like you have to check with regex and case when statement in a subquery. Then perchance you can add with row_number().....over(...) in outer query.

MBR
Enthusiast

Re: Need help on Converting from Characters into Integer

Thank you Raja.

in Target we have only unique values, sample values i have mentioned above.

yes we have used Like() operater and row_number() over()...its working fine for now..but do we have any teradata specific function to resolve this issue.

Thanks,

Balaji

MBR
Enthusiast

Re: Need help on Converting from Characters into Integer

here is my query...

i need to get the only numbers in highlighted part(CAST(TRIM(s.custId1) AS INTEGER) (FORMAT '9(9)') (CHAR(20))

select * from  Database.table1 s
inner  join Database.table2 c
on CAST(TRIM(s.custId1) AS INTEGER) (FORMAT '9(9)') (CHAR(20))= c. custId1
where c.column1= 'Y'
and cast(c.column2 as date format 'yyyymmdd')(char(8)) = '99991231'
and c.custId1 is not null;
Senior Apprentice

Re: Need help on Converting from Characters into Integer

What's the datatypes of those custID1s?

Why do you cast to INT/DATE and back to VARCHAR?

MBR
Enthusiast

Re: Need help on Converting from Characters into Integer

Thanks Dieter for your reply,

CustID1 is Char datatype.

And we casting the column1,column2 for filter

main issue is with the custId1, if we get any alphabets/characters then we need to replace with numbers. (CAST(TRIM(s.custId1) AS INTEGER) (FORMAT '9(9)') (CHAR(20)))

Could you please suggest how can we resolve this.

Thanks,

Balaji