Display only 10 charecters from column [message #659973] |
Mon, 06 February 2017 23:24 |
anil_mk
Messages: 146 Registered: August 2006 Location: Bangalore, India
|
Senior Member |
|
|
Dear all,
I have a below table with data as below
create table test_chr10(col1 CHAR(15));
INSERT INTO test_chr10
SELECT '1234567890' FROM DUAL UNION ALL
SELECT ' 1234567890 ' FROM DUAL UNION ALL--SPACE AT START AND END TO BE EXCLUDED AS DIGITS ARE 10 DIGITS
SELECT '123456789 ' FROM DUAL UNION ALL-- REQUIRE 1 TO 9 DIGITS AND LAST SPACE ALSO WHICH BECOMES 10 CHARS
SELECT ' 123456789' FROM DUAL UNION ALL-- REQUIRE 1 TO 9 DIGITS AND FIRST SPACE ALSO WHICH BECOMES 10 CHARS
SELECT ' 12345678 ' FROM DUAL UNION ALL-- REQUIRE 1 TO 8 DIGITS AND FIRST AND LAST SPACE ALSO WHICH BECOMES 10 CHARS
SELECT ' 12345678' FROM DUAL UNION ALL--REQUIRE FIRST 2 SPACES AND 1 TO 8 DIGITS
SELECT '12345678 ' FROM DUAL; --REQUIRE 1 TO 8 DIGITS AND LAST 2 SPACES
COMMIT;
Please help me to write query to display only 10 charecters including space/spaces if space/spaces are within 10 charecters.
Thanks
|
|
|
|
|
|
Re: Display only 10 charecters from column [message #660003 is a reply to message #660002] |
Tue, 07 February 2017 04:32 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
SIDE NOTE: Seems the forum software is hiding some of the spaces. If you copy and paste the above into something that'll show the spaces there is only 1 before and after, but if you edit the post you can see 2 before and after.
|
|
|
|
|
|
|
Re: Display only 10 charecters from column [message #660013 is a reply to message #660012] |
Tue, 07 February 2017 05:38 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
If you've got trailing space because it's a char datatype then I would have thought that
" 12345678 "
should be
" 12345678"
not
"12345678 "
The first 2 spaces are input by the user, last 2 added by the datatype. So I would have thought you'd want to get rid of the ones the datatype added before touching the ones that came from user input.
EDIT: missed a digit
[Updated on: Tue, 07 February 2017 05:38] Report message to a moderator
|
|
|
|
|
Re: Display only 10 charecters from column [message #660016 is a reply to message #660015] |
Tue, 07 February 2017 07:07 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
If you want only the first 10 characters no matter that is in the string then
substr(col1,1,10)
If you only want the first 10 characters after stripping out preceding spaces then
substr(ltrim(col1),1,10)
if you only want the first 10 characters after stripping out leading and trailing spaces tehn
substr(trim(col1),1,10)
|
|
|