Home » SQL & PL/SQL » SQL & PL/SQL » Display only 10 charecters from column (Oracle 11g Release 2 Linux)
Display only 10 charecters from column [message #659973] Mon, 06 February 2017 23:24 Go to next message
anil_mk
Messages: 144
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 #659978 is a reply to message #659973] Tue, 07 February 2017 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What is the result for " 12345678 "?

Re: Display only 10 charecters from column [message #660001 is a reply to message #659978] Tue, 07 February 2017 04:25 Go to previous messageGo to next message
anil_mk
Messages: 144
Registered: August 2006
Location: Bangalore, India
Senior Member

Thanks Michel for reply, result for " 12345678 " should be same as it has 10 charecters. I need 10 charecters result set.
Re: Display only 10 charecters from column [message #660002 is a reply to message #660001] Tue, 07 February 2017 04:28 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No there were 12 characters, 2 blanks before and 2 after.
Say we use the same question but with dots instead of blanks.
What would be the result for "..12345678.."?

Re: Display only 10 charecters from column [message #660003 is a reply to message #660002] Tue, 07 February 2017 04:32 Go to previous messageGo to next message
cookiemonster
Messages: 12992
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 #660007 is a reply to message #660003] Tue, 07 February 2017 05:04 Go to previous messageGo to next message
anil_mk
Messages: 144
Registered: August 2006
Location: Bangalore, India
Senior Member

If we have 2 spaces at start and 2 spaces at end and 1 to 8 digits, should fetch only 10 charecters i.e. 1 to 8 digits and last 2 spaces i.e. "12345678 "

Thanks
Re: Display only 10 charecters from column [message #660008 is a reply to message #660007] Tue, 07 February 2017 05:20 Go to previous messageGo to next message
cookiemonster
Messages: 12992
Registered: September 2008
Location: Rainy Manchester
Senior Member
What if it's
"..1234567.."

(dots = spaces obviously)

Or - what's the general rule for where to leave spaces when spaces have to used to make up the 10 characters?
Re: Display only 10 charecters from column [message #660011 is a reply to message #660008] Tue, 07 February 2017 05:28 Go to previous messageGo to next message
anil_mk
Messages: 144
Registered: August 2006
Location: Bangalore, India
Senior Member

we don't have .., only spaces, spaces are because of CHAR datatype.

Thanks
Re: Display only 10 charecters from column [message #660012 is a reply to message #660011] Tue, 07 February 2017 05:35 Go to previous messageGo to next message
cookiemonster
Messages: 12992
Registered: September 2008
Location: Rainy Manchester
Senior Member
Did you miss the bit where I pointed out that multiple spaces don't display properly?
dots = spaces.
So each dot above represents a space in your data.
Re: Display only 10 charecters from column [message #660013 is a reply to message #660012] Tue, 07 February 2017 05:38 Go to previous messageGo to next message
cookiemonster
Messages: 12992
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 #660014 is a reply to message #660013] Tue, 07 February 2017 05:48 Go to previous messageGo to next message
anil_mk
Messages: 144
Registered: August 2006
Location: Bangalore, India
Senior Member

Yes, in above example we need last 2 spaces.

Thanks
Re: Display only 10 charecters from column [message #660015 is a reply to message #660014] Tue, 07 February 2017 06:07 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why not the first 2 ones?
What if
" 12345678   "
or
"   12345678 "

What is the general rule to keep spaces?

[Updated on: Tue, 07 February 2017 06:09]

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 Go to previous message
Bill B
Messages: 1709
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)
Previous Topic: DBLinks stopped after wrongly changing domain name in sqlnet.ora
Next Topic: AND & OR Condition Usage
Goto Forum:
  


Current Time: Sat Jan 20 06:00:30 CST 2018

Total time taken to generate the page: 0.01698 seconds