Split A String Up If It Has Space?

Jun 19, 2008

i have a column called name in a table. now what iwould like to do is to check if it has two parts "paulh some" and then output the second part!

SELECT LTRIM(name,' '), length(name) length
FROM list
WHERE INSTR(name,' ') = 1;

but that doesnt work.. the fucntion is NOT checking for the space! if i use another character (a or b etc) it works..

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Split A String

Dec 21, 2011

I have strings like

1) ICE_10001 ICE_10002 ICE_10003

2) ICE_10005 ICE_10006

i want to split above strings like

1)ICE_10001
2)ICE_10002
3)ICE_10003
4)ICE_10005
5)ICE_10006

If it is possible in oracle sql

View 15 Replies View Related

Split A String In Oracle

Sep 27, 2010

I have a string like '9999999;A' one field as numeric & other as char.Now i want to split this string into two values removing the ; delimiter in oracle using for loop.

View 2 Replies View Related

SQL & PL/SQL :: Split Long String

Feb 19, 2013

how I can change this function to pass argument that length is 32676 .

create or replace TYPE "HRS_SPLIT_TBL_T" as table of varchar2(32767);

create or replace function hrs_split
(
p_list varchar2,
p_del varchar2 := ','
) return hrs_split_tbl_t pipelined
is

[Code]...

I got error String literal too long when execute select below

select distinct COLUMN_VALUE Tbat_latn
from table(
HRS_SPLIT( 'PER0000002,PER0000094,PER0000094,PER0000096,PER0000096,
PER0000024,PER0000024,SAB0000001,SAB0000001,PER0000002,
PER0000096,PER0000094,PER0000094,PER0000002,PER0000024,
PER0000024,PER0000096,PER0000096,PER0000094,PER0000094,
PER0000002,PER0000024,PER0000024,PER0000096,PER0000096,
PER0000094,PER0000094,PER0000002,PER0000024,PER0000024,

[Code]....

View 12 Replies View Related

SQL & PL/SQL :: Split String In Two Halves?

Oct 23, 2013

I want to split any user given string into two parts, how can I do it.

suppose some sample inputs may be:

'123456' splits into '123','456' ,'123456789' splits into '12345','6789'

NOTE: the first half splitted string length is greater in case of odd no. string as in second example

How can I do it?

View 13 Replies View Related

SQL & PL/SQL :: Function To Split A String?

Oct 18, 2004

I have a string like 10.01.03.04.234 or 234.05.07.032.� Is there a simple function in Oracle that would give me

10
01
03
04
234 as seperate values in a select clause.

I would like to do a

Select function(10.01.03.04.234)
into var1, var2, var3, var4, var5
from ....

I can accomplish the same with substr/length/instrs in a procedure...but looking for a simple function, if any, instead of writing a proc/function....

View 39 Replies View Related

PL/SQL :: Split String Values

Feb 28, 2013

I have a column in a table that contains a string seperated by .

e.g.

IT.HARDWARE
IT.APPS
IT.SOFTWARE

I would like to split the two values out on two columns e.g.

Column1 - IT
Column2 - Hardware
etc.

View 3 Replies View Related

Create View Off Split String

May 22, 2012

How do i split a string based on a space. And then create a view with three columns of that split string.

For example if i have a table with Full name as a column how do i split that string and create a view with firstname, middlename, lastname as column names with the split string as data.

If you need anything clarifying write back.

View 1 Replies View Related

SQL & PL/SQL :: Split A String As Per Required Format

Dec 23, 2010

Split a column with values like 1-2-21-3 into 001-002-21-003 (ie format required is 000-000-00-000) using sql.

View 3 Replies View Related

PL/SQL :: Split Comma Separated String

Mar 11, 2013

I am trying to split comma separated string. My table has more than 5 lacks data. I have tried the following SQL but its taking more than 5 minutes. Any Alternative solution to return data quickly ?

SELECT REGEXP_SUBSTR(order_id, '[^,]+', 1, LEVEL) order_id
FROM order_detail
CONNECT BY REGEXP_SUBSTR(order_id,'[^,]+',1,LEVEL) IS NOT NULL

SELECT REGEXP_SUBSTR(order_id, '[^,]+', 1, LEVEL) order_id
FROM order_detail
CONNECT BY LEVEL <= LENGTH(order_id) - LENGTH(REPLACE(order_id, ',')) + 1

View 3 Replies View Related

SQL & PL/SQL :: Query To Split Character String Using XQUERY

Aug 5, 2010

I am using this query to split numeric values i.e ('1,2,3,4,5,6')but when i am trying with char value i.e ('a,b,c,d'), its not working.

select id
FROM employee e
WHERE e.id IN
( SELECT TO_NUMBER(xt.column_value)
FROM XMLTABLE('1,2,3,4,5,6') xt );

View 6 Replies View Related

SQL & PL/SQL :: Split String Function For Large Number Of Records?

Aug 6, 2010

I have created a function that is used for splitting a comma separated string & give the output in tabular form.here is the function

Here I have used CLOB as my input string will be huge(greater than max limit of varchar2)

CREATE OR REPLACE TYPE SPLIT_TBL_CLOB AS TABLE OF CLOB;
CREATE OR REPLACE FUNCTION CSVTOSTRING_CLOB
(
P_LIST CLOB,
P_DEL VARCHAR2 := ','
) RETURN SPLIT_TBL_CLOB PIPELINED

[code]....

But here I am facing 2 problems.

1. The function is not accepting a large string & I am getting the error

ORA-01704: string literal too long

2. The function is going for an infinite loop.

View 10 Replies View Related

SQL & PL/SQL :: String Split / Replace Based On Character Length

Jun 19, 2013

notes column having 2000 characters max, i want my string output based on 35 characters, ya i need to replace tag after 30 characters in the string.. I need out put as "hi hello how are you doing out there, similar i need to calculate the sting length and have to split it 35+35+35..

This i tried

select substr(note,1,(instr(note, ' ',35)))||'
'||substr(note,instr(note, ' ',35),(instr(note, ' ',35)))notes from test

View 7 Replies View Related

SQL & PL/SQL :: Space In Every Character Of A String With Single Query

Mar 19, 2012

How can we get 'space in every character of a string with Single select query'

for example:-
string 'INDIA'
result should be 'I N D I A'

View 1 Replies View Related

SQL & PL/SQL :: Replace Double Space With Single Space And Also Remove Junk Characters

Jul 1, 2013

I want to replace double space with single space and also remove junk characters from the data. How can I do that?

CREATE TABLE test07013
(
NAME VARCHAR2(50)
);
INSERT INTO VALUES ('WARREN,'); -- REMOVE ","
INSERT INTO VALUES ('CLARK H'); -- REPLACE "DOUBLE SPACE" WITH "SINGLE SPACE"
INSERT INTO VALUES ('BRYAN A.'); -- REMOVE "."
INSERT INTO VALUES ('CARTER JR. ROBERT'); -- REMOVE "."," AND REPLACE "DOUBLE SPACE" WITH "SINGLE SPACE"

View 8 Replies View Related

Any Way To Free Space From Sysaux Table Space?

Oct 3, 2012

Just now sysaux resized to 600m from 250m >>

Sysaux Tablespace is running low. WE SET AWR RETENTION TIME=60 DAYS. WE ARE NOT INTEREST TO EXTEND SYSAUX TABLESPACE SIZE.
Usually we take AWR weekly once. Some times we did ADDM report and ASH.

CODEsql>select TABLESPACE_NAME, FILE_NAME, BYTES/(1024*1024), AUTOEXTENSIBLE, MAXBYTES/(1024*1024)  from dba_data_files where tablespace_name = 'SYSAUX';

TABLESPACE_NAME       FILE_NAME                                             BYTES/(1024*1024)     AUT         MAXBYTES/(1024*1024)
SYSAUX                  /u01/app/oracle/oradata/test/sysaux01.dbf           600                  YES                 32767.9844
CODEsql> @SCRIPT.SQ

TABLESPACE   TOTAL_SPACE(MB)    USED_SPACE(MB)   FREE_SPACE(MB)     % Used    % Free
SYSAUX          600                  248            352               41.33        58.67

1. What's the best SOLUTION ?
2. Can i shrink sysaux tablespace ?
3. I think , The size for all occupants in sysaux tablespace is less than 200 MB => how to find actual content of sysaux tablespace ?
4. What could be the reason for growth? Is there any way to free the space from sysaux table space?

View 9 Replies View Related

ORA-02085 - Database Link String Connects To String

Jun 19, 2012

I have this error (and solution):

ORA-02085: database link string connects to string

Cause: a database link connected to a database with a different name. The connection is rejected.

Action: create a database link with the same name as the database it connects to, or set global_names=false.
Where should I set global_names=false ?

View 7 Replies View Related

SQL & PL/SQL :: How To Find Whether Exact String Is Present / Not In Given String

Mar 14, 2013

I'm facing some problem even after using INSTR function in Oracle.The problem is I have written the logic in the PL/SQL block which appends all the values fetched in a loop on the basis of whether the string is present or not.

For ex:

The first value fetched from the select query first is ABCDEFG which gets appended to a variable
The next value fetched is AB even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The next value fetched is BCDEF even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The third Value fetched is ABCDEFG this will not get appended presently according to the logic which is correct.

writing that piece of code to append the value fetched which doesn't exactly match with the existing string

View 3 Replies View Related

How To Use String Buffer Instead Of String Query

May 9, 2008

show an ex to use string buffer for select statemnt

View 1 Replies View Related

SQL & PL/SQL :: Table Being Split Into Two

Jun 2, 2011

i'm working on this database assignment...and basically, no matter what I do my table seems to automatically split into a different table after 8 rows...i've tried googling it and using commands like....

set wrap off;
set numwidth 20;

and fiddling with the format of each column but nothing seems to work...i just want my table to show as one table

here's my script
create table patient
(patient_number number(4) primary key,
patient_name varchar(15) NOT NULL,
address varchar(30) NOT NULL,
telephone number(7) NOT NULL,
patient_status char(1) NOT NULL,
next_appt date,
balance number(5,2),
CHECK (balance >= 0),
CHECK (patient_status='N' OR patient_status='A' OR patient_status='I'));
[code]...

12 rows selected.the columns are actually aligned so don't worry about that...it's just the splitting of the rows.

View 5 Replies View Related

PL/SQL :: How To Split Strings

Oct 18, 2013

PROCEDURE 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.

View 13 Replies View Related

Split Value And Sort By Lastname?

Dec 11, 2008

I have a field called fullname that outputs records with fullname of people.

Here is what I have when I do this sql:

Select fullname from tableOne;

John Jones
Bill Aronsen
Sam Baker
George Williams
Dave Smith

I would like to sort in order of last name but cant figure out how to do the sql:

Bill Aronsen
Sam Baker
John Jones
Dave Smith
George Williams

View 1 Replies View Related

SQL & PL/SQL :: How To Split Values From A Column

Jul 14, 2010

How to split the values from a column?

For example: i had table T1 with below structue

Table T1

userid
-------
sandy1234
raj6785
Andrew12367
Michael56098

i need output like below structure

Nameid
---- ----
Sandy1234
Raj6785
Andrew12367
Michael56098

View 5 Replies View Related

SQL & PL/SQL :: Split Gender To Two Column?

Feb 8, 2012

I have a table like follows

Name Gender
-------------
Arun M
Anitha F
Bala M
Banu F

I need the output as follows

Male Female
-----------------
Arun Anitha
Bala Banu

What are the ways can we generate the above query

View 14 Replies View Related

SQL & PL/SQL :: Split Csv To Multiple Records

Jun 18, 2013

I have a small requirement...

Create table temp_a (source_code varchar2(100), target_code varchar2(1000));

Insert into temp_a values ('1','002.0 AND 002.9');
Insert into temp_a values ('2','729.90 AND 079.99 AND 002.9');

Output :

1 002.0
1 002.9
2 729.90
2 079.99
2 002.9

So, once we get the output, it needs to be joined to another table. I did Google search, but most of them are retuning collections / arrays as output. Not sure how I join the collection with the table.

create or replace function splits
(
p_list varchar2,
p_del varchar2
) return split_tbl pipelined
is
l_idx pls_integer;
[code].......

View 3 Replies View Related

SQL & PL/SQL :: Split Result Set Into Column

Aug 17, 2012

i have a table emp with three column

columns are (empid varchar,empnomini varchar,nominitype varchar), data in table like

empid empnomini nominitype
1 x B
1 y c
2 xx B
2 yyyy c

and i want data comes like

empid nominitype b nominitype c
1 x y
2 xx yyyy.

View 6 Replies View Related

SQL & PL/SQL :: Split Ranges In Database?

May 5, 2010

I have been wracking my brain on this. Is there a way to write an SQL code that will combine split ranges within a table?

SAMPLE_TABLE
Common_FieldLow_ValueHigh_Value
1123123
11243000
130023005
130064000
135003501
130064500

I would like to combine any ranges that may exist. It is also possible that some row ranges may be nested in other rows.

END_RESULT
Common FieldLow ValueHigh Value
11233000
130024500

View 9 Replies View Related

PL/SQL :: Split Column Values

Mar 11, 2013

I have a requirement in which the amount need to be split in to multiple period.

For example if there amount of 3000 and start and end date is 01-jan-2013 and 31-mar-2013 then the output of the query should be

Name Start Period End Period Jan-13 Feb-13 Mar-13
------------------------------------------------------------------------------
XXX 01-JAN-13 31-MAR-13 1000 1000 1000

How to achieve this.

View 2 Replies View Related

Use Split Partition Option?

Jan 28, 2013

I am using 11.2.0.2 database.

I got ORA-14074 error says that if you have a MAXVALUE already you will need to either drop the partition that encompasses the MAXVALUE or split partition.So I want to use split partition option through the below statement,

alter table
tablename
split partition
partmax at (XXX)
into
(partition partXXX, partition partmax);

I am concerned about the existing data in the table will it by any change gets deleted, and what about indexes, do I have to rebuild, etc.I have to do this on produciton. I cannot test this as I don't have a test environment and moreover the table is having 70 million records and we don't have time/hardware resources to recreate this by export import in another database.

View 6 Replies View Related

PL/SQL :: Query To Split Field Value

Jul 31, 2013

In employees table I am having employee_name column data as follows 

Aaron, Mrs. Jamie (Jamie)Aaron, Mrs. Jenette (Jenette)Abbott, Ms. Rachel (Rachel) Breton, Mr. Jean  Britz, Mrs. Sarie (Sarie) --> Now, I want to display the employee name like "Mrs. Jamie" (with out Surname and with out bracket included text),-->

How to achieve this by SQL query.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved