SQL & PL/SQL :: Procedure To Remove Extra Spaces In Strings
Feb 17, 2011I would like to create a procedure in oracle to remove extra spaces coming as blank spaces(squared ones)in strings.
View 32 Replies
ADVERTISEMENT
PL/SQL :: Remove Spaces Between Two Number From A Varchar2?
Oct 18, 2012I have a question respect to remove spaces from a varchar2.
The varchar2 is '7987451 1234567' and i need that string like '7987451 1234567', because the field has a length of 15.
i try with this, but does nothing
Select TRIM('7987451 1234567') from dual
Other possibilities are find several spaces and try to replace with only one, but would a heavy work (may be with sentences Loop).
This problem is now in our loading processes. Exists some function to replace spaces between numbers of varchar2?
SQL & PL/SQL :: Remove Blank Spaces In The Query Output
May 6, 2010I write a Query to get Deptno wise Employee Names as fallows..
SQL> select max(decode(d.deptno,10,e.ename)) dept10,
2 max(decode(d.deptno,20,e.ename)) dept20,
3 max(decode(d.deptno,30,e.ename)) dept30
4 from emp e,
5 dept d
6 where e.deptno = d.deptno
[code]....
But i don't want to include the blank(null) spaces in the query output.
The Required output should be like this..
DEPT10 DEPT20 DEPT30
------ ------ ------
CLARKADAMSALLEN
KINGFORDBLAKE
MILLERJONESJAMES
SCOTTMARTIN
SMITH TURNER
WARD
Client Tools :: How To Remove Spaces After Data
Aug 14, 2012I have a little problem, using SQLPLUS I use "set linesize 300", so in the END of output file file. I got spaces after info ! I mean
select client_id || ';' || date || ';' || amount info
from table
1;FEB-2012;5000........
2;DIC-2011;10000.......
500;MAR-2012;300000.... (. = spaces)
How I remove spaces after Data ?
SQL & PL/SQL :: Remove Spaces By Excluding Double Quotes From A String
Oct 29, 2012I want to remove more than one space from a string by excluding double quotes.
For example:
I/P: Item .getChildByType(" Agreement").getParent( ) .hasChildByType("Agreement ")
O/P : Item.getChildByType(" Agreement").getParent().hasChildByType("Agreement ")
Extra Long Stored Procedure Inputs
Mar 20, 2007I am modifying a stored proc that i wrote a couple of years ago. The change is that i want certain inputs to accept strings of characters (comma delimited) so they can be parsed and processed in the proc. The problem is, when i pass very little data (about 50 characters) It fails saying the character sting buffer is to small.
I have setup the input as a type of a column that is a clob and i have also tried using just vahrchar2 (with no size set).
Client Tools :: Remove / Prevent PL/SQL Procedure Successfully Completed Message
Aug 20, 2010I do not want the "PL/SQL procedure successfully completed." message in my output file - I don't see/recognise an option to suppress or remove this.
-- Test Proc
--
set linesize 500
set pagesize 50000
set tab off
set wrap off
set serveroutput on
set feedback on
[code]...
PL/SQL procedure successfully completed.
SQL & PL/SQL :: Eliminate Spaces Between Values
Nov 2, 2011i can't eliminate the spaces between values, i tried to use rtrim but still failed.
Set pagesize 0
set linesize 1000
set heading off
set feedback off
set colsep '|'
SELECT '200', '20002977', T0.TP, T0.Description, T2.FirstName, T2.LastName, 'Geography Code', SUBSTR(T3.aoManager, -6,5)
[code]....
SQL & PL/SQL :: Search Spaces In Column
Mar 15, 2010in my databse i have column name as email_id
in this column email id of cutomers are stored but in some emaild contains space
i want to find out such email id
How To Handle Spaces In Path Name Of File With PL/SQL
Apr 10, 2009How do I handle spaces in the path name of a file with PL/SQL? This is what I am trying to do.
start f:folder namefolder name with spacesfoldernamescript.sql
I keep getting an error:
"The file 'f:folder.sql' does not exist."
Is there a special character I need to put in there for the spaces? How can I get this to work?
SQL & PL/SQL :: Query To Eliminate Multiple Spaces?
Mar 5, 2010I need to eliminate the blank spaces based on below conditions Consider name column with a value as
Input : "sa c h in Te nd ulka r" where "Sachin" is first name and "Tendulkar" is last name. there is more than 1 space between sachin and tendulkar (here its not displaying properly)
Condition :Second name is seperated from first name with more than 1 spaces and others are with 1 black space. I need to get result as Output:"sachin Tendulkar" ( there should be 1 blank space between first and last name in result.)
Forms :: Cuts Off Spaces At The End Of Textfields?
Oct 30, 2012forms cuts off spaces at the end of textfields.
Is there any way to prevent it?
Retrieving Extra Rows
Dec 14, 2011I have this SQL that returns the correct amount of rows which should be 2:
Select Distinct A.File_Name, A.File_Desc, A.file_location,
A.location_date, A.downloaded_date, A.downloaded_id, A.file_size,
A.days_to_request, B.File_Name, B.Act_Date, B.date_loaded
from SDT_LOG A Inner Join ACTIVITY_LOG B
On A.file_name = B.file_name
and A.downloaded_date = B.date_loaded
I need to add another field in the Select query which is B.Act_Code. When I do, I get 2 extra rows. I do not know how to make these rows distinct.
The A table's structure is along with sample data for 1st record:
CODE Example of
Name Type 1st record.
---- ------- --------------
FILE_NAME VARCHAR2(50) STLMK.txt
FILE_DESC VARCHAR2(50) NON-RESIDENT
FILE_LOCATION VARCHAR2(50) L:\NonResFiles
YEAR NUMBER(4) 2008
LOCATION_DATE DATE 10/10/2007
DOWNLOADED_DATE DATE 09/04/2008 9:17:00 AM
DOWNLOADED_ID VARCHAR2(50) Cindy
FILE_SIZE CHAR(10) 16212
DAYS_TO_REQUEST NUMBER(3) 60
The B table's structure is along with sample data for 1st record:
CODE Example of
Name Type 1st record
---- ------ -----------
FILE_NAME VARCHAR2(50) STLMK.txt
ACT_CODE CHAR(2) D
ACT_DATE DATE 10/10/2007
ACTIVITY_ID VARCHAR2(50) downloaded on
DATE_LOADED DATE 09/04/2008 9:17:00 AM
The second record of activity would all be the same except Cindy would be "Jason", act_code would be an "S", activity_id would be "sent on" and then of course the dates would be changed to whenever the new information was saved within the system.
I am getting something like this (shortened of course):
CODEFile_name Downloaded_ID Act_Code
STLMK.txt Cindy D
STLMK.txt Cindy S
STLMK.txt Jason D
STLMK.txt Jason S
There should only be one row for Cindy with a D act_code and one row for Jason with an S act_code. For some reason, Cindy and Jason each get a row with the different act_code. I'm retrieving 4 rows instead of two when I use B.Act_Code in the SQL statement.
Cindy should have the D Act_Code because she downloaded that file name and Jason should have the S because he sent that file to someone else. Every time a file's activity changes, it is entered into the system so we can keep track of where the files are.
Cindy should have the D Act_Code because she downloaded that file name and Jason should have the S because he sent that file to someone else. Every time a file's activity changes, it is entered into the system so we can keep track of where the files are.
Also, I get the 2 extra rows when I add activity_id field to the select.
I use Oracle 10.
SQL & PL/SQL :: Retrieving Extra Rows
Dec 14, 2011I have this SQL that returns the correct amount of rows which should be 2:
Select Distinct A.File_Name, A.File_Desc, A.file_location,
A.location_date, A.downloaded_date, A.downloaded_id, A.file_size,
A.days_to_request, B.File_Name, B.Act_Date, B.date_loaded
from SDT_LOG A Inner Join ACTIVITY_LOG B
On A.file_name = B.file_name
and A.downloaded_date = B.date_loaded
I need to add another field in the Select query which is B.Act_Code. When I do, I get 2 extra rows. I do not know how to make these rows distinct.
The A table's structure is along with sample data for 1st record:
Example of
Name Type 1st record.
---- ------- --------------
FILE_NAME VARCHAR2(50) STLMK.txt
FILE_DESC VARCHAR2(50) NON-RESIDENT
FILE_LOCATION VARCHAR2(50) L:NonResFiles
YEAR NUMBER(4) 2008
LOCATION_DATE DATE 10/10/2007
DOWNLOADED_DATE DATE 09/04/2008 9:17:00 AM
DOWNLOADED_ID VARCHAR2(50) Cindy
FILE_SIZE CHAR(10) 16212
DAYS_TO_REQUEST NUMBER(3) 60
The B table's structure is along with sample data for 1st record:
Example of
Name Type 1st record
---- ------ -----------
FILE_NAME VARCHAR2(50) STLMK.txt
ACT_CODE CHAR(2) D
ACT_DATE DATE 10/10/2007
ACTIVITY_ID VARCHAR2(50) downloaded on
DATE_LOADED DATE 09/04/2008 9:17:00 AM
The second record of activity would all be the same except Cindy would be "Jason", act_code would be an "S", activity_id would be "sent on" and then of course the dates would be changed to whenever the new information was saved within the system.
I am getting something like this (shortened of course):
File_name Downloaded_ID Act_Code
STLMK.txt Cindy D
STLMK.txt Cindy S
STLMK.txt Jason D
STLMK.txt Jason S
There should only be one row for Cindy with a D act_code and one row for Jason with an S act_code. For some reason, Cindy and Jason each get a row with the different act_code. I'm retrieving 4 rows instead of two when I use B.Act_Code in the SQL statement.
Cindy should have the D Act_Code because she downloaded that file name and Jason should have the S because he sent that file to someone else. Every time a file's activity changes, it is entered into the system so we can keep track of where the files are. Cindy should have the D Act_Code because she downloaded that file name and Jason should have the S because he sent that file to someone else. Every time a file's activity changes, it is entered into the system so we can keep track of where the files are.
Also, I get the 2 extra rows when I add activity_id field to the select.I use Oracle 10.
PL/SQL :: Timestamps - Extra Zero Are Adding?
Jul 8, 2013view the below select statement..why it's adding extra zero's...
select to_timestamp('2001-05-22 12:00:18.600','YYYY-MM-DD HH:MI:SS.ff3AM') from dual
output: 5/22/2001 12:00:18.600000000 PM ---why it's adding extra zeors's
my output should be as " 5/22/2001 12:00:18.600 PM"
Reports & Discoverer :: Removing Spaces From Top Of Report
Aug 18, 2010I am using 10g reports.
I created a report (its basically a statement) without a header. When I run the report, blank lines appear at the top of the report before the details start printing. How do I get the report to print from the top of the page without leaving any blank lines.
SQL & PL/SQL :: Insert Into Table And Add Extra Field Value
Sep 19, 2011I need to copy records from a working table to a history table. I have the following sql statement
insert into test.history
(equip_ID, state, manufacturer, install_year, capacity,
group_ID, Test_status)
select (equip_ID, state, manufacturer, install_year, capacity,
group_ID, Test_status
from test.info_AP
Table test.history has one more field in it called test_year. I need to fill this field when I do the insert. Can't use an after update trigger as the field is currently set to not allow nulls.
SQL & PL/SQL :: Linking Two Tables With Extra Field
Oct 1, 2011Once I add an extra column to link the two tables the number of rows is reduced by one, however when I try to get this extra record I find none matching the same criteria that led to reducing the records.
SQL> SELECT count(*)
2 FROM N_CONTRACT NC, N_WITHDRAWAL_REQUEST NWR
3 WHERE NC.fk_temP_withdrawal_req_serial = NWR.SERIAL_NUMBER;
COUNT(*)
----------
2243
[code]...
PL/SQL :: An Extra Parameter For Summary Function
Dec 19, 2012Why cant oracle give an extra parameter for summary function like MAX and MIN...For example
select max(salary,2)
from empWill give the second max salary.
SQL & PL/SQL :: Extra Characters While Utl_file.get_line()
Jul 21, 2010Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
i am trying to read the file, for which i m using the following:
l_utlfile_hdr := utl_file.fopen(p_dir,'header_evnt.txt', 'R');
utl_file.get_line(l_utlfile_hdr, l_hdr_evnt);
it gets these three characters at the start of the line
l_hdr_evnt = 
So i saved header_evnt.txt as UTF-8, and used utl_file.fopen_nchar, utl_file.get_line_nchar, which got rid of first two characters, but still has ¿. How to get rid of that?
JDeveloper, Java & XML :: Removing White Spaces From Arraylist
Mar 30, 2012I'm trying to remove whitespaces from user input of an ArrayList.
.trim() method for String, but what's the method for ArrayList?
Extra Space In File_location For Alter Command
Nov 7, 2010I do below alter command
alter tablespace RCA_MB_IDX add datafile '/gxsdb/database/oradata5/RCA/RCA_REF_MB_IDX_01.dbf ' size 1024M
But there is extra space in the file location '/gxsdb/database/oradata5/RCA/RCA_REF_MB_IDX_01.dbfX'
I mark X for that space ...
The command is commit .
But there is problem when do backup .
Server Utilities :: Migration From Postgres Into Oracle Columns With Spaces
Jul 21, 2010I have migrated from postgres to oracle entire databases however some tables have spaces how can they be removed and how can i found out all the tables in a schema with spaces?
View 8 Replies View RelatedPL/SQL :: Trim Extra Space In Function Parameter List
May 9, 2013I am calling a function in front end, from front end i am passing space, now i want to trim space in parameter itself, it's psssible, assume the below is the function defination, i want to trim the p_region_name parameter like this trim(p_region_name), is this possible?
FUNCTION add_country_region_column (
p_s_country_code_iso_2 IN varchar2_table_type,
p_type IN d_country.c1_type%TYPE,
p_name IN d_country.c1_name%TYPE,
p_desc IN d_country.c1_desc%TYPE,
[Code]....
SQL & PL/SQL :: To Compare Two Strings
Apr 8, 2008I have requirement wherein i need to compare two strings (with multiple words) and it should return the %(percentage) of comparison.
e.g. "oracle infotech" and "infotech oracle" are 100% match
Do we have any oracle built ins to compare ?
SQL & PL/SQL :: String Between Two Strings?
Apr 4, 2012I have to fetch a string which is between to constant strings in a column.
Ex: Test Column
"The Student Record 10101 is deleted"
"The Student Record 10102 is deleted"
"The Student Record 10103 is deleted"
3 rows.
In this i need to fetch only ID from each row.
create table testtable ( TestCol varchar2(4000));
INSERT INTO TESTTABLE VALUES ('The Student Record 10101 is deleted');
INSERT INTO TESTTABLE VALUES ('The Student Record 10102 is deleted');
INSERT INTO TESTTABLE VALUES ('The Student Record 10103 is deleted');
PL/SQL :: How To Split Strings
Oct 18, 2013PROCEDURE COLUMN_SPLIT (p_def IN VARCHAR2, p_sch OUT VARCHAR2, p_table OUT VARCHAR2, p_column OUT VARCHAR2)
IS
BEGIN
NULL;
END;
END;
I want to split p_def by dots, check for 3 elements, and return them in p_sch, p_table and p_column for example p_sch will be like hello.howare.you.I want to split it to hellohowareyouI have very limited knowledge with pl/sql.
Server Administration :: Extra Space In File_location For Alter Command?
Nov 7, 2010I do below alter command alter tablespace RCA_MB_IDX add datafile '/gxsdb/database oradata5/RCA/RCA_REF_MB_IDX_01.dbf ' size 1024M
But there is extra space in the file location '/gxsdb/database/oradata5 RCA/RCA_REF_MB_IDX_01.dbfX' I mark X for that space ...
The command is commit .
But there is problem when do backup ...as the file cannot be find since there is extra space after ".dbf" How can I fall back ?
SQL & PL/SQL :: Convert Columns To Rows With Extra Hard Coded Column
Mar 30, 2012I've a query that sum up data from one table. But I've to convert them into rows.
Summary query result.
COL1 COL2 COL3 COL4
1000 2000 3000 4000
Test Table creation script.
CREATE TABLE TEST_TBL
(
COL1 NUMBER(14,2),
COL2 NUMBER(14,2),
COL3 NUMBER(14,2),
COL4 NUMBER(14,2)
) ;
INSERT INTO TEST_TBL VALUES( 1000, 2000, 3000, 4000 ) ;
Expected result
A1 1000
A2 2000
A3 3000
A4 4000
A1, A2, A3, A4 are hard coded fixed values.
I could have done this but not a good idea in case table TEST_TBL is not a single row table but an inline query on 1,00,00,000 records with summary functions. In my table I've a summary query instead of single row table.
SELECT 'A1', COL1 FROM TEST_TBL
UNION ALL
SELECT 'A2', COL2 FROM TEST_TBL
UNION ALL
SELECT 'A3', COL3 FROM TEST_TBL
UNION ALL
SELECT 'A4', COL4 FROM TEST_TBL
Sorting Strings As Integers
Jun 10, 2007The db field is a string-type field that hold strings such as:
'1234'
'753'
'textstring'
'345'
Obviously, if you sort it, it'll be stored as a string such as:
'1234'
'345'
'753'
'textstring'
My client wants it so the numbers sort as integers, followed by string-like strings (sorted alphabetically), so it's like.
'345'
'753'
'1234'
'textstring'
Is there a quick and dirty SQL-only way to doing this in Oracle?