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


ADVERTISEMENT

PL/SQL :: LENGTH (String) Ignores Special Character %?

Oct 10, 2012

why the below is not the same?

select * from textsearch where
LENGTH(TRIM(BOTH ' ' FROM Text)) < 1
--0 Rows

select * from textsearch where Text='%';
--12 Rows

View 1 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

ORA-01458 - Invalid Length Inside Variable Character String

Sep 30, 2010

Where I am copying the data from one database to another I am getting this error.

ORA-01458: invalid length inside variable character string

This is applicable for only character string.

The source Database is
Oracle9i Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for HPUX: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

The destination database is
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
PL/SQL Release 9.0.1.1.1 - Production
CORE 9.0.1.1.1 Production
TNS for 32-bit Windows: Version 9.0.1.1.0 - Production
NLSRTL Version 9.0.1.1.1 - Production

View 4 Replies View Related

Precompilers, OCI & OCCI :: ORA-01458 / Invalid Length Inside Variable Character String

Apr 16, 2009

I want to insert data into a RAW column in a table. The below code snippet is used for the same

OCIRaw **l_rawPtr = new OCIRaw*[10];
for(int i=0;i<10;i++)
{l_rawPtr[i] = NULL; }
int max = 0;
for(int i=0;i<10;i++)
{
OCIRawAssignBytes(l_environment, l_error, l_char, strlen((const char*)l_char), &l_rawPtr[i])

[code].....

When the statement is getting executed, i am getting the following error. Error - ORA-01458: invalid length inside variable character string

View 3 Replies View Related

PL/SQL :: How To Select String Between Nth Specific Character To Nth+1 Character

Aug 9, 2012

I have one string 'SWAP_20120224_MEGAMART_MAR_Sales.csv'

I want to get the string between second underscore and third underscore i.e. 'MEGAMART'

How to achieve this in sql statement?

View 4 Replies View Related

Client Tools :: Replace & With Any Character

Dec 22, 2010

I want replace '&' with any character.But when i use '&' in select statement.then it ask the value.

View 6 Replies View Related

SQL & PL/SQL :: Replace New Line Character In Clob

Sep 2, 2010

Replacing a new line character in clob column i Oracle 10g.I have tried replacing the new line using regexp_replace,replace and translate. I have also tried using lob_relace procedures.

View 2 Replies View Related

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

SQL & PL/SQL :: Special Character Conversion / Translate / Replace

Nov 3, 2010

I have requirement to cleanup special character of field. The data which is retrieved from the field and written to text file.

So Here

Ã- should be replaced by i
HÃ-­re - I need value Hire(actual value) from the filed.
í- this has written into file instead of i

In database i have the following

NLS_NCHAR_CHARACTERSET - AL16UTF16
NLS_CHARACTERSET - UTF8

I tried this

select convert('HÃ-re','UTF8') from dual
------------------------
Output: H??re

View 6 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

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

SQL & PL/SQL :: Replace String In 9i?

Jun 26, 2012

I want to replace numeric values of a specific format with 'X' , find the below example and note that the string in the example only for sample values and the strings may be different.

Eg.

Input String :

Ticket no 12343 , 1234567891234567 , origin-dxb , dest-lhr , 1234 5678 9012 2345 , address - rose wood
bldg 2444 , downtown ,london-33 .

Output string :

Ticket no 12343 , XXXXXXXXXXXXXXXX , origin-dxb , dest-lhr , XXXX XXXX XXXX XXXX , address - rose wood
bldg 2444 , downtown

View 6 Replies View Related

PL/SQL :: Replace A String

Sep 26, 2013

I have a string. For example "I have too many files. There are 1000 files. I have to delete them." Sometimes the string can be "I have too many files. There are 115003 files. I have to delete them." Whatever the srting is, I need to change the string to "I have too many files. There are 10 files. I have to delete them." replace the "1000" or "115003" to "10". This portion of the string is always an integer. I use Oracle 11G2. 

View 6 Replies View Related

SQL & PL/SQL :: Select Length - String Literal Too Long

Jun 21, 2011

If i try to find length of the string with more than 4000 char in SQL Developer it throws error "ORA-01704: string literal too long". if anything i need to SET in preference.

select length('string with more than 4000 char) from dual;

View 10 Replies View Related

SQL & PL/SQL :: Function To Check Length And Build A String?

Dec 18, 2012

I have created a VIEW with a column containing user-defined aggregated values(stores the output in varchar2 data type). Some of the records exceed the maximum limit, for example varchar2(4000). I was suggested a PL/SQL function to check the length and build a string if necessary..

CREATE OR REPLACE
FUNCTION SUBURL(col1 IN VARCHAR2, col2 IN varchar2)
RETURN varchar2

[Code].....

Here, I got the following error,

Error(4,25): PLS-00103: Encountered the symbol "," when expecting one of the following:
:= . ( @ % ; not null range default character

View 14 Replies View Related

PL/SQL :: Add CRLF After String Reaches A Certain Length (in Inches)

Apr 17, 2013

I have this function to calculate the length of the string in inches.

I would like to know how can I add a line break or CRLF right after X length and not to break any words in the string.?

View 2 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

PL/SQL :: How To Split Column Data Based On Identifier

Oct 3, 2012

I am using oracle database.

I am having data in this format in my column 1234~2345~3456~4567.

I need a query to split the data in the column based on the identifier '~',so that i can pick out the value after the second occurrence of the identifier.

View 3 Replies View Related

SQL & PL/SQL :: PLS-00215 - String Length Constraints Must Be In Range In Blob

Sep 28, 2011

I want to read content from blob column and write into file using dbms_output.put_line. See below code. i am getting PLS-00215: String length constraints must be in range (1 .. 32767) error.

set serveroutput on
DECLARE
l_file UTL_FILE.FILE_TYPE;

[Code]....

the length of the blob column is 1081656. so i have changed in raw(1081656) in above code. after the execution, i am getting the below error PLS-00215: String length constraints must be in range (1 .. 32767).

View 5 Replies View Related

SQL & PL/SQL :: Replace Characters In A String?

Apr 7, 2011

replace the first 5 commas with the character '|' in the below string:

'Red, White, Blue, Purple, Pink, Green, Yellow, Gold and many others, like Black and Silver'

I tried:

SELECT regexp_replace('Red, White, Blue, Purple, Pink, Green, Yellow, Gold and many others, like Black and Silver',
',','|',1,5) from dual

but it only replaces the 5th comma.

View 3 Replies View Related

SQL & PL/SQL :: Replace With The First Occurrence In The String

Nov 12, 2012

I am using regexp_replace function to replace the string between the double quotes with the first occurance but i am able to replace first occurance but I am not getting remaining string.

For example:

select REGEXP_replace('Parent.addChildByName("DS-Id of OAL(BROBA)")||parent.add("DS-Id of OAL(BROBA)")','["]:print:+:punct::print:*["]','XXXX') from DUAL

O/P: Parent.addChildByName(XXXX)

Expected O/P : Parent.addChildByName(XXXX)||parent.add("DS-Id of OAL(BROBA)")

View 4 Replies View Related

PL/SQL :: Replace The Digits After A String CVV With X?

Jul 13, 2012

I am using the oracle 10g, I am trying to write the a sql query by which in a below mention patterns I can replace the digits after a string 'CVV' with X. The no of X will be equal to the no of digits after CVV

1. BTA CVV 8810
2. VISA PARTICULAR CCVIXXXXXXXXXXXX5474/1012 CVV498
3. C***CVV VI 569***
4. dskdfjdkgjdfk: FP CCVI,XXXXXXXXXXXX0031/0711/CVV 063/dffddf:dfdfdfd
5. (T) CVV 4671
6. MS-ACEEML/CVV
7. O/AXXXXXXXXXXXX1007EXP1210/CVVXXXXXXXXXXX4664/MEETING CARD FOR AMEX PARTNER OFFICES

AFTER a change above data should look like as mentioned below

1. BTA CVV XXXX
2. VISA PARTICULAR CCVIXXXXXXXXXXXX5474/1012 CVVXXX
3. C***CVV VI XXX***
4. dskdfjdkgjdfk: FP CCVI,XXXXXXXXXXXX0031/0711/CVV XXX/dffddf:dfdfdfd
5. (T) CVV XXX
6. MS-ACEEML/CVV -- no change for this
7. O/AXXXXXXXXXXXX1007EXP1210/CVVXXXXXXXXXXX4664/MEETING CARD FOR AMEX PARTNER OFFICES --- -- no change for this

Initially I tried to find the position of CVV in any string and then from that position take the digit and replace them with X. I tried the below code which is not complete

select REGEXP_INSTR('BTA CVV XXXX','CVV',1,1) from dual;

View 11 Replies View Related

Query To Split Records Based On Values From Another Table?

Feb 20, 2012

I have a table with following values in a column

Table A

col1
10
35
20
25

I need to form a query which will take these four values in rownum part and split the records into 4 groups in Table B.

Table B- 90 records (10 + 35 + 20 + 25)

Now for example, the Table B is having emp no, order by ascending and i need to split into 4 groups,

with first group having start value -1 and end value -10

second group - start value -11 and end value-45

third group - start value -46 and end value -65

fourth group - start value - 66 and end value-90

one way i can do it by using union and count, which was a bit tedious if the no. of group goes upto 10.

note that the values in Table A is dynamically changing, so not able to hard code values.

View 2 Replies View Related







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