June 2, 2009 at 11:54 pm
Hi!
How can I find how many characters are in one field, the value in the field is without spaces!
example...
Field1
001234
Any system function?
June 3, 2009 at 12:06 am
Oracle length() function will count the number of chaarcters in a field.
select id, name, length(name) from test_table;
ID NAME LENGTH(NAME) Notes.
--- ---------- ------------- ---------------------------------------------
1 A B C 5 5 charcters with 2 spaces.
2 D E 5 2 characters with leading and trailing blanks
3 A501 4 4 characters
4 A502 4 4 characters
5 000123 6 6 Number in a varchar field.
5 rows selected
If you have a number in a varchar field it will count the 0's as characters.
If you want to count the number of digits without the leading zero's, you can either convert it to a number, or use trim() .
June 3, 2009 at 12:12 am
thepotplants (6/3/2009)
Oracle length() function will count the number of chaarcters in a field.select id, name, length(name) from SAPR3.test_table
[font="Courier New"]ID NAME LENGTH(NAME)
--- --------- ----------------------
1 A B C 5
2 D E 5
3 A501 4
4 A502 4
5 000123 6 [/font]
5 rows selected
If you have a number in a varchar fields it will count the 0's as characters.
Yea the length func returns the number of chars, but little bit confused how to use it as filter for example give me all records from a table where field characters are 6...
SELECT * FROM TABLE
WHERE LENGTH(FIELD) = 6;
But as I know it will boom the performance, couz I have also other criteria for selection!
Anyway, thank you!
:hehe:
June 3, 2009 at 12:19 am
Dugi (6/3/2009)
Yea the length func returns the number of chars, but little bit confused how to use it as filter for example give me all records from a table where field characters are 6...
SELECT * FROM TABLE
WHERE LENGTH(FIELD) = 6;
Yep, thats perfect.
June 3, 2009 at 8:35 am
Dugi (6/3/2009)I know it will boom the performance
Oh yes... lenght() function would ensure you do Full Table Scan on the target table plus adding the overhead of a function call for each row in the table.
A lot of fun! 😀
_____________________________________
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.Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply