-
Converting numeric values..
Hi,
I am having 5 digit numeric field and I want to convert the number by incrementing one on each digit.
for ex:
12345 should be converted as 23456
56789 should be converted as 67890
I thought of adding 11111, but it fails when the field has '9'.
Is there any shortest way to do ?
-
1- Convert your numeric field into a string
2- Build a conversion table... 1234567890 / 2345678901
3- Process each element in your string field replacing chars as per conversion table
4- Convert your string field back to a number
Good Luck
PS: I don't even want to know why you need to do that
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
This?
Code:
to_number(translate(to_char(my_column,'0123456789','1234567890'))) ...
-
Good job David
Just a small syntax issue, try this:
to_number(translate(to_char(my_column),'0123456789','1234567890'))
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
-
Thanks a lot David and PAVB ....
Its working fine ...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|