All Forums Database
MBR 29 posts Joined 01/13
27 Apr 2015
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

Bala
Raja_KT 1246 posts Joined 07/09
27 Apr 2015

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....

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

MBR 29 posts Joined 01/13
27 Apr 2015

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)
 
 

Bala

Raja_KT 1246 posts Joined 07/09
27 Apr 2015

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.
 
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

MBR 29 posts Joined 01/13
27 Apr 2015

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

Bala

MBR 29 posts Joined 01/13
28 Apr 2015

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;

 

Bala

dnoeth 4628 posts Joined 11/04
28 Apr 2015

What's the datatypes of those custID1s?
Why do you cast to INT/DATE and back to VARCHAR?

Dieter

MBR 29 posts Joined 01/13
28 Apr 2015

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

Bala

You must sign in to leave a comment.