hmm...

my colleague ask me for help but i do not know too but i'm eager to know as well.

the question is this: example there is a column postal which consists of postal codes from many countries and the user needed to differentiate Singapore from other countries according to the postal code. As you all know, the postal code is different for Singapore because it have six numbers and other countries might have 4 or 5.

Therefore within each postal code value, is there any possible way to count the how many numbers it have?

starsunited,

Try this:

SELECT * FROM sometable WHERE LENGTH(postal) = 6

but then it's not a fixed number

so can it be like select character_length(postal) from table;?
i check the manual then the character_length is only for strings? will it wrk for numbers as well?

the Length function is used for all characters regardless whether it's numbers or strings?

so if its numbers?

this sql query is logical?

select length(to_char(postal)) from table;

Originally Posted by starsunited

but then it's not a fixed number

so can it be like select character_length(postal) from table;?
i check the manual then the character_length is only for strings? will it wrk for numbers as well?

the Length function is used for all characters regardless whether it's numbers or strings?

As I said in the PM, I don't know what it is you are after now. Are you trying to pull all the records which meet a certain criteria (6-character postal codes)? Or are you trying to compute the length for a value in a given column? And if so, do you want to return all the rows in the table or just one?

Give me a very specific narrative of what is in the column and what it is you want out of your query.

Originally Posted by starsunited

so if its numbers?

this sql query is logical?

select length(to_char(postal)) from table;

I just don't understand what you are trying to say here. Try to expand your descriptions more and use a more complete description.

As for this query, you will end up with all the rows returned from the table because you have no WHERE clause. The values returned will only be a function of the data in the postal column rather than the data itself.

So say your single-column table looks like this:


postal
-------
21817
21878
CD4EJC
3892999


The values returned from the query you suggest will probably look something like this:

-------
5
5
6
7

that's precisely what i want to do but thanks anyway to show an example output from my query