View previous topic :: View next topic
|
Author |
Message |
maxsubrat
Active User
Joined: 27 Feb 2008 Posts: 110 Location: india
|
|
|
|
Hi,
I have a requirement to fetch emp-id which are 10 characters(digits and alphabets) only.
I tried with the below query,
SELECT SUBSTR(EMP_ID,1,10)
FROM PO.ACVFTPO ;
I am getting output of emp-id with 9 characters only, not 10 characters records...
Can someone give some idea... if we fetch more than 9 characters also, i think it is ok. but the problem is the emp-id is a alphanumeric.
Thanks |
|
Back to top |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009 Posts: 565 Location: Bangalore
|
|
|
|
Try something like
Code: |
SELECT EMP_ID
FROM PO.ACVFTPO
where STRIP(LENGTH(EMP_ID)) = 10 ; |
Assuming EMP_IS is CHAR column. |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
Code: |
STRIP(LENGTH(EMP_ID)) |
I am pretty sure that no dbms will ever support columns/fields with a length expressed by a 10 digit number
most probably it would be wiser to code
Code: |
LENGTH(STRIP(EMP_ID)) |
|
|
Back to top |
|
|
gylbharat
Active Member
Joined: 31 Jul 2009 Posts: 565 Location: Bangalore
|
|
|
|
Thanks Enrico... Typo Error |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10872 Location: italy
|
|
|
|
I knew it was a typo... just picking a bit |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
maxsubrat wrote: |
Hi,
I tried with the below query,
SELECT SUBSTR(EMP_ID,1,10)
FROM PO.ACVFTPO ;
I am getting output of emp-id with 9 characters only, not 10 characters records...
Thanks |
Code: |
SELECT EMP_ID FROM PA.ACVFTPO;
|
This will get you the full EMP_ID. What is the definition of EMP_ID and have you checked to see if there are any with more then 9 characters? |
|
Back to top |
|
|
maxsubrat
Active User
Joined: 27 Feb 2008 Posts: 110 Location: india
|
|
|
|
Thanks enrico and gilbharat... query working fine ... |
|
Back to top |
|
|
|