Home » SQL & PL/SQL » SQL & PL/SQL » truncate data after 10 digits
truncate data after 10 digits [message #209455] Thu, 14 December 2006 23:55 Go to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Hi All,

I want to truncate the line after 10characters.
like
I am having result of SQL as below

cellno
4199129169898
I want to see just first 10 characters. I am looking length function but can't make it working.

regards
dil
Re: truncate data after 10 digits [message #209459 is a reply to message #209455] Fri, 15 December 2006 00:23 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Why can't you use substr?
substr(your_string,1,10)
If at all you have only numbers, you can use to_number on top of that.

By
Vamsi
Re: truncate data after 10 digits [message #209472 is a reply to message #209455] Fri, 15 December 2006 01:24 Go to previous messageGo to next message
k7nixen
Messages: 7
Registered: December 2006
Junior Member
select to_number(substr('4199129169898',1,10)) as value from dual;

VALUE
----------
4199129169
Re: truncate data after 10 digits [message #209473 is a reply to message #209472] Fri, 15 December 2006 01:31 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
@Zafer
Don't spoon feed.
That's what I also have been told in the beginning.
Let him think.

Thanks,
Vamsi
Re: truncate data after 10 digits [message #209693 is a reply to message #209473] Sun, 17 December 2006 01:33 Go to previous messageGo to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Thanks All for your quick reply. You are also right Vamsi. But sometimes people are new to field and they want to fix the issues to save there jobs.

Thanks again for your help

Warm Regards
Dil
Re: truncate data after 10 digits [message #209696 is a reply to message #209693] Sun, 17 December 2006 01:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I'm very sorry if this sounds harsh, but if you needed this to save your job, you are definitely in the wrong job...
Re: truncate data after 10 digits [message #209767 is a reply to message #209696] Sun, 17 December 2006 17:25 Go to previous messageGo to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Thanks for all the replies. I will try to do spend some more time in reading SQL book. But anyway thanks for all of you Gurus.
I was in bit rush to send this message. I should not done this.
Have a nice Christmas and Safe New Year to all of ORAFAQ members.
You all are wonderful people!!

Regards
DIL
Re: truncate data after 10 digits [message #209986 is a reply to message #209472] Mon, 18 December 2006 19:14 Go to previous messageGo to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Hi Vamsi,k7nixen,

With all your hints i have written the query with substr and works fine but having only one issue
I am concatenating 4 fields in the SQL with field size that i am trimming with substr.

select distinct
substr(parent,1,12) || to_char(substr(folder,1,12)) || ltrim(substr(cellnum,1,11),'M') || lpad(amount,6,'0') as Master_Report
from
configuration_item;

What i want is if there is less field size than defined it shold give us spaces. But the output of this script is leaving no space.

example
I want output like this

parent folder cellnum amount
NDEPT907301 CB3980103122 011162599 000100

but i am getting like

parent folder cellnum amount
NDEPT907301CB3980103122011162599000100


I cellnum coloum i am having M infront of cellnum. This is the reason i am trimming M.

I have tried

select distinct
substr(parent,1,12) || ' ' ||to_char(substr(folder,1,12)) || ' ' ||ltrim(substr(cellnum,1,11),'M') || lpad(amount,6,'0') as Master_Report
from
configuration_item;
But then it is putting spaces in all the fields.

Please help

Regards
Dil





Re: truncate data after 10 digits [message #210004 is a reply to message #209986] Mon, 18 December 2006 22:26 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Hi Sydney1,

great to see your are trying . now your problem is that query is putting spaces in every field you mean to say between cellnum and amount also?

what do you mean by :

Quote:
I cellnum coloum i am having M infront of cellnum. This is the reason i am trimming M.


The output is not putiing space between cellnum and amount so will you place a space between them?

Bye
Re: truncate data after 10 digits [message #210009 is a reply to message #210004] Mon, 18 December 2006 22:38 Go to previous messageGo to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Thanks for your reply Trivedi,

What i want is fixed output of 42 characters including spaces.
for 4 coloumns.
My problem is if there is 40 characters the output is of 40characters i want every time output should be 42 characters.

like if customer added only 10characters in Parent folder. the output still show 12 characters with 2 spaces. and so on

parent folder cellnum amount
NDEPT907301 CB398010312 0111625999000100

above output customer put 11 characters instead of 12 so there should be space between parent and folder.

but i am getting like

parent folder cellnum amount
NDEPT907301CB398010312011162599000100

I think you will understand bit better to help me

Regards
Dil
Re: truncate data after 10 digits [message #210044 is a reply to message #210009] Tue, 19 December 2006 01:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can use RPAD(string,n,' ') to return a string padded to length n with spaces on the right hand side. If string was longer than n to start with, then it will return the first n chrs of string.
Re: truncate data after 10 digits [message #210054 is a reply to message #210009] Tue, 19 December 2006 02:48 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
hi dil,

i think this will help you:

Quote:
select distinct
substr(rpad(parent,12,' '),1,12) || ' ' ||
to_char(substr(folder,1,12)) || ' ' ||
ltrim(substr(cellnum,1,11),'M') ||
lpad(amount,7,'0') as Master_Report
from
configuration_item


Try this and let me know.

Bye
Ashu
Re: truncate data after 10 digits [message #210083 is a reply to message #209455] Tue, 19 December 2006 05:11 Go to previous messageGo to next message
k7nixen
Messages: 7
Registered: December 2006
Junior Member
This is what you need :

SELECT SUBSTR(RPAD(parent,12,' '),1,12) || RPAD(TO_CHAR(substr(folder,1,12)),12,' ') || RPAD(LTRIM(substr(cellnum,1,11),'M'),11,' ') ||
LPAD(amount,7,'0') as Master_Report
FROM configuration_item

[Updated on: Tue, 19 December 2006 05:15]

Report message to a moderator

Re: truncate data after 10 digits [message #210211 is a reply to message #210054] Tue, 19 December 2006 19:26 Go to previous messageGo to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Thanks Trivedi and k7nixen,

You both are helpful persons. I have added what you have suggested as below

SELECT
SUBSTR(RPAD(parent,12,' '),1,12) ||
TO_CHAR(substr(folder,1,12)) ||' '
LTRIM(substr(cellnum,1,11),'M') ||
LPAD(amount,7,'0') as Master_Report
FROM configuration_item

and getting output like
NDEPT907300 CB398010319 0411077825000100
NDEPT907300 CB398010319 0411077841000100
NDEPT9073011CB3980103122 014500020000000
0

NDEPT9073011CB3980103122 041102509400010
0



See line one output is fine. But line 2 and 3 there is space between folder and cellnum. I don't want any space there as folder is alreadt 12characters. I want cellnum only 10digits and if more than 10 truncate like we are doing for folder and cellnum.

Origainal data from cellnum is like
M0411077841
M0145000200

This is the reason i am using LTRIM(substr(cellnum,1,11),'M')
But the output should only be 10 digits not more than that. Otherwise the program loads wrong data.

As program is picking first 12 digits for parent.next 12 for folder and next 12 for cellnum and next 6 for amount. if it is not that characters it will give blank spaces.

I hope you will get more information. As i am only DBA here and trying my best on festive session to deliver with you guys help.

Warm Regards


Re: truncate data after 10 digits [message #210221 is a reply to message #210054] Tue, 19 December 2006 21:20 Go to previous messageGo to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Hi Trivedi and k7nixen,


All works now and really i am thanking you from bottom of my heart. as i am new to SQL and spend 2 days only reading SQL.
Have a nice Christmas and new Year.
Final script is as below;


SELECT
SUBSTR(RPAD(parent,12,' '),1,12) ||
RPAD(to_char(substr(vci.folder,1,12)),12,' ')||
LTRIM(substr(cellnum,1,11),'M') ||
LPAD(amount,7,'0') as Master_Report
FROM configuration_item

Regards
dil


Re: truncate data after 10 digits [message #210222 is a reply to message #210083] Tue, 19 December 2006 21:31 Go to previous messageGo to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Hi

just last question regarding the script.

SELECT
SUBSTR(RPAD(parent,12,' '),1,12) ||
RPAD(to_char(substr(vci.folder,1,12)),12,' ')||
LTRIM(substr(cellnum,1,11),'M') ||
LPAD(amount,6,'0') as Master_Report
FROM configuration_item


Means this script will return only 10digits for cellnum and 6 digits for amount too or i need to change the query. As i can't change the data and test. but there was already wrong data entered for parent and folder to test. so i can confirm that script is working for parent and folder. Can i say it will work for cellnum and amount too.

thanks
Dil

Re: truncate data after 10 digits [message #210239 is a reply to message #209455] Wed, 20 December 2006 00:22 Go to previous messageGo to next message
k7nixen
Messages: 7
Registered: December 2006
Junior Member
I didnt understand what you are asking?
Re: truncate data after 10 digits [message #210628 is a reply to message #210222] Thu, 21 December 2006 10:13 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
hi sydney,

You question is :
Quote:
Can i say it will work for cellnum and amount too.


Yes it will work. and read more about the string functions.

Bye
Ashu
Re: truncate data after 10 digits [message #210697 is a reply to message #210628] Thu, 21 December 2006 17:50 Go to previous message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Thanks for all the help guys
Yes it all works fine

Regards
Previous Topic: Transaction log
Next Topic: materialized views log creation
Goto Forum:
  


Current Time: Thu Dec 08 21:48:43 CST 2016

Total time taken to generate the page: 0.06554 seconds