Home » SQL & PL/SQL » SQL & PL/SQL » RDB7 Equivalent function of LEN(), or any way
RDB7 Equivalent function of LEN(), or any way [message #270248] Wed, 26 September 2007 05:22 Go to next message
ljudilla677
Messages: 13
Registered: September 2007
Junior Member
Hi guys i would like to know if its possible to determine the number of characters in an integer data type field just like LEN() function.

CHARACTER_LENGTH(), OCTET_LENGTH() ETC., just won't work on integer or numeric data types.
Re: RDB7 Equivalent function of LEN(), or any way [message #270250 is a reply to message #270248] Wed, 26 September 2007 05:36 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Quote:

determine the number of characters in an integer data type


May be you need to know the number of bytes of an integer Data.
You can use vsize function

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/functions213.htm

[Updated on: Wed, 26 September 2007 05:36]

Report message to a moderator

Re: RDB7 Equivalent function of LEN(), or any way [message #270252 is a reply to message #270250] Wed, 26 September 2007 05:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you want to know the number of non-zero digits in an integer, then there are several ways of doing it:
floor(log(10,<number>))+1

or
length(to_char(<number>,'999999999999999999999'))
are the two that leap to mind first.
Re: RDB7 Equivalent function of LEN(), or any way [message #270253 is a reply to message #270248] Wed, 26 September 2007 05:53 Go to previous messageGo to next message
ljudilla677
Messages: 13
Registered: September 2007
Junior Member
Thanks alot guys, heres the details of the problem. By the way this is RDB7 and is not supporting newer version of oracle SQL syntaxes.

::::::::::::structure:::::::::::::
:::::::::::::::table: ntcenh
:::::::::::::::field: transaction_time
:::::field data type: integer
::::::::::::::length: 6

::::::::::::sample data:::::::::::
:::::transaction_time
:::::--------------------
:::::133442
::::::91121
:::::180111


*Note*
:::::This is in military time format 24H. Unfortunately the previous programmer of this one is not minding the impact of data extraction. Thats why i get this kind of problem.

:problem:
:::::I want to check if the value of that field contains 6 characters or less.

[Updated on: Wed, 26 September 2007 06:04]

Report message to a moderator

Re: RDB7 Equivalent function of LEN(), or any way [message #270255 is a reply to message #270248] Wed, 26 September 2007 06:00 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
First convert transaction_time to char and then use length function
length(to_char(transaction_time))
Re: RDB7 Equivalent function of LEN(), or any way [message #270259 is a reply to message #270248] Wed, 26 September 2007 06:08 Go to previous messageGo to next message
ljudilla677
Messages: 13
Registered: September 2007
Junior Member
Thanks Muzahidul,

I tried it but i get this error.


SQL> select length(to_char(transaction_time)) from ntcenh where transaction_date
=20070807;
%SQL-F-RTNNOTDEF, function or procedure TO_CHAR is not defined
SQL>


Re: RDB7 Equivalent function of LEN(), or any way Wed, 26 September 2007 06:00
muzahidul islam
First convert transaction_time to char and then use length function

length(to_char(transaction_time))
Re: RDB7 Equivalent function of LEN(), or any way [message #270262 is a reply to message #270253] Wed, 26 September 2007 06:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
    transaction_time=trunc(transaction_time)
and transaction_time between 1 and 235959

Regards
Michel

Re: RDB7 Equivalent function of LEN(), or any way [message #270272 is a reply to message #270262] Wed, 26 September 2007 06:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There's a little problem with this question - I rather think it's concerning Oracle RDB7 - NOT the standard Oracle Db V7.

Question to the OP - do you have any of the extension functions installed in you Rdb (See after the boxes in this Link)

I think you probably want to use Character_Length, although you might need to CAST it as some sort of string first.

{typos}

[Updated on: Wed, 26 September 2007 06:33]

Report message to a moderator

Re: RDB7 Equivalent function of LEN(), or any way [message #270273 is a reply to message #270248] Wed, 26 September 2007 06:32 Go to previous messageGo to next message
ljudilla677
Messages: 13
Registered: September 2007
Junior Member
hi Michel,

I quite don't get the reason behind your code. Anyway i tried it but heres the result.

SQL> select length(trunc(transaction_time)) nhdbf, transaction_time from ntcenh where transaction_date=20070807 limit to 2 rows;
****NHDBF*****TRANSACTION_TIME
******4***********11124*******
******4***********81622*******
2 rows selected
SQL>

Still, I expect that the field NHDBF should have this results:
****NHDBF*****TRANSACTION_TIME
******5***********11124*******
******5***********81622*******
but unfortunately it didn't.


Thanks alot,


Lemuel



Re: RDB7 Equivalent function of LEN(), or any way
Michel Cadot

transaction_time=trunc(transaction_time)
and transaction_time between 1 and 235959


Regards
Michel
Re: RDB7 Equivalent function of LEN(), or any way [message #270275 is a reply to message #270248] Wed, 26 September 2007 06:38 Go to previous messageGo to next message
ljudilla677
Messages: 13
Registered: September 2007
Junior Member
Hi JRowbottom,

Your definitely right there, i already tried "select length(cast(transaction_time as integer)) from ntcenh;" but the result is all 4. dunno know how to make it display the expected
result.

I can't access the link you posted there probably because of our firewall or proxy settings here.


Thanks,

Lemuel



Re: RDB7 Equivalent function of LEN(), or any way [message #270272 is a reply to message #270262 ] Wed, 26 September 2007 06:30 Go to previous messageGo to next message
JRowbottom
Messages: 2244
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
add to buddy list
ignore all messages by this user
There's a little problem with this question - I rather think it's concerning Oracle RDB7 - NOT the standard Oracle Db V7.

Question to the OP - do you have any of the extension functions installed in you Rdb (See after the boxes in this Link

I think you ptrobably want to use Character_Length, although you might need to CAST it as an Integer

Re: RDB7 Equivalent function of LEN(), or any way [message #270283 is a reply to message #270275] Wed, 26 September 2007 06:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

You want to check if data are correct, I gave you a condition to meet (necessary but not sufficient).
If your data are of integer datatype, you can remove the first part.

Regards
Michel
Re: RDB7 Equivalent function of LEN(), or any way [message #270291 is a reply to message #270248] Wed, 26 September 2007 07:22 Go to previous messageGo to next message
ljudilla677
Messages: 13
Registered: September 2007
Junior Member
Hi Michel,

Thanks alot,

heres the code:
select length(trunc(transaction_time)) nhdbf, 
       transaction_time 
  from ntcenh 
  where transaction_date=20070807 limit to 2 rows;


the result is this:
    NHDBF    TRANSACTION_TIME
      4         11124
      4         81622


My expected result should be like this:
    NHDBF    TRANSACTION_TIME
      5         11124
      5         81622




Thanks alot,

Lemuel
Re: RDB7 Equivalent function of LEN(), or any way [message #270294 is a reply to message #270291] Wed, 26 September 2007 07:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try
select character_length(cast(transaction_time as integer)) nhdbf, 
       transaction_time 
  from ntcenh 
  where transaction_date=20070807 limit to 2 rows;

Re: RDB7 Equivalent function of LEN(), or any way [message #270299 is a reply to message #270248] Wed, 26 September 2007 08:05 Go to previous messageGo to next message
ljudilla677
Messages: 13
Registered: September 2007
Junior Member
Hi JRowbottom,

I did tried it but the result is this:
SQL> select character_length(cast(transaction_time as integer)) nhdbf,
cont>        transaction_time
cont>   from ntcenh
cont>   where transaction_date=20070807 limit to 2 rows;
       NHDBF   TRANSACTION_TIME
           4              11124
           4              81622
2 rows selected
SQL>



Thanks,

Lemuel

Re: RDB7 Equivalent function of LEN(), or any way [message #270300 is a reply to message #270299] Wed, 26 September 2007 08:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What if you'd concatenate an empty string to the number, would that not make it a string?
I don't remember the concatenator for Rdb7, but that should not be so hard to find.
Re: RDB7 Equivalent function of LEN(), or any way [message #270306 is a reply to message #270248] Wed, 26 September 2007 08:18 Go to previous messageGo to next message
ljudilla677
Messages: 13
Registered: September 2007
Junior Member
Hi Frank,


Wow, that idea might work. I'll try it.


Thanks alot,


Lemuel
Re: RDB7 Equivalent function of LEN(), or any way [message #270310 is a reply to message #270248] Wed, 26 September 2007 08:43 Go to previous messageGo to next message
ljudilla677
Messages: 13
Registered: September 2007
Junior Member
Hi All,

Thanks alot everyone, i already found a good solution.

Heres the code:

SQL> select greatest(transaction_time, 99999) TDigit, transaction_time from ntce
nh limit to 7 rows;
      TDIGIT   TRANSACTION_TIME
       99999              85507
       99999              90046
       99999              61459
      103800             103800
      181307             181307
      181341             181341
      181442             181442
7 rows selected
SQL>


With this i can use DECODE() function to give me my expected results. Whew, it took me alot of time and with all your help, i finally got it.



Thanks alot,


Lemuel
Re: RDB7 Equivalent function of LEN(), or any way [message #270316 is a reply to message #270310] Wed, 26 September 2007 09:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
out of curiosity, what does
select character_length(cast(transaction_time as integer)) nhdbf, 
       cast(transaction_time as integer) cast_time
       transaction_time 
  from ntcenh 
  where transaction_date=20070807 limit to 2 rows;
give you?
Re: RDB7 Equivalent function of LEN(), or any way [message #270424 is a reply to message #270248] Wed, 26 September 2007 20:48 Go to previous messageGo to next message
ljudilla677
Messages: 13
Registered: September 2007
Junior Member
Hi JRowbottom,

This is the whole story of that code:

select 	decode(99999, greatest(transaction_time, 99999), 
        cast(substring(cast(transaction_time as char(5)) from 1 for 1) as integer) * 3600 + 
        cast(substring(cast(transaction_time as char(5)) from 2 for 2) as integer) * 60 + 
        cast(substring(cast(transaction_time as char(5)) from 4 for 2) as integer), 
        cast(substring(cast(transaction_time as char(6)) from 1 for 2) as integer) * 3600 + 
        cast(substring(cast(transaction_time as char(6)) from 3 for 2) as integer) * 60 + 
        cast(substring(cast(transaction_time as char(6)) from 5 for 2) as integer)) As ConvSecs, 
        transaction_time 
from ntcenh limit to 7 rows;


And heres the output:

cont> transaction_time from ntcenh limit to 7 rows;
             CONVSECS   TRANSACTION_TIME
                32107              85507
                32446              90046
                22499              61459
                38280             103800
                65587             181307
                65621             181341
                65682             181442
7 rows selected


With that, i have converted the elapsed transaction time (in integer data type) into seconds. From there i can do my computations correctly.

Thanks alot guys.


Thanks alot,


Lemuel
Re: RDB7 Equivalent function of LEN(), or any way [message #270426 is a reply to message #270316] Wed, 26 September 2007 21:00 Go to previous message
ljudilla677
Messages: 13
Registered: September 2007
Junior Member
Hi JRowbottom,

select character_length(cast(transaction_time as integer)) nhdbf, 
       cast(transaction_time as integer) cast_time
       transaction_time 
  from ntcenh 
  where transaction_date=20070807 limit to 2 rows;


This code will give you the lenght of the datatype, not the length of the value. which means if you design the field in an integer data type with 4 bytes or 4 characters, then no matter how you try the function octet_length(), character_length(), length() etc., still the result will be the fixed datatype length is returned. This functions are good when your trying to get the length of a non fixed datatype such as varchar, varchar2 etc.

That code give me an output of 4, all the way to the bottom of the recordset.

Thanks JRowbottom.



Thanks alot,


Lemuel

[Updated on: Wed, 26 September 2007 23:53]

Report message to a moderator

Previous Topic: Eliminating the duplicate records
Next Topic: To get all privileges, roles granted to a user
Goto Forum:
  


Current Time: Sun Dec 11 06:01:01 CST 2016

Total time taken to generate the page: 0.05761 seconds