count length

[Cretin]

Is there a way in PL/SQL to display a count of how many rows in a particular column has a certain length minus leading blanks?

For example how many rows have a customer number less than 9 characters ignoring leading blanks.

Cretin

 

[taupirho]

To get rid of leading blanks use the LTRIM function in Oracle.

So something like

select count(mycol)
from mytable
where length(ltrim(mycol)) < 10





In order to understand recursion, you must first understand recursion.

 

[carp]

You don't need PL/SQL for this.
Try

SELECT count(*) 
  FROM (SELECT my_column 
          FROM my_table
         WHERE length(ltrim(my_column)) = 25);

Just substitute 25 with the length you want.

 

[Cretin]

Worked like a champ thank you.

Cretin

 

[SantaMufasa]

Cretin,

Out of curiosity, why did you specify, "Is there a way in PL/SQL to ..."?

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[Beilstwh]

What about null columns

select count(*)
from mytable
where nvl(length(ltrim(mycol)),0) < 10

Bill
Lead Application Developer
New York State, USA

 

[SantaMufasa]

What about null columns

select count(*)
  from mytable
 where nvl(length(ltrim(mycol)),0) < 10

One could replace the "NVL(<expression>,0)" with "NVL(<expression>,999999999999999999999)" it seems to me.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.