Home > Default > Breaking the string into different columns

Breaking the string into different columns

November 30Hits:0
Hi Guys,
I need to break the following string into different columns
I am trying to write it using instr and substr , but having some issues .
Is there any other way to do this. If not can someone help me , below is the query that i am working on
SELECT SUBSTR ('XXXXXX.0001.09011.0001.00002.03.0004.0005.0006.00007', 1, INSTR ('XXXXXX.0001.09011.0001.00002.03.0004.0005.0006.00007', '.', 1) - 1) col1,
SUBSTR ('XXXXXX.0001.09011.0001.00002.03.0004.0005.0006.00007',
INSTR ('XXXXXX.0001.09011.0001.00002.03.0004.0005.0006.00007', '.', 1) + 1,
INSTR ('XXXXXX.0001.09011.0001.00002.03.0004.0005.0006.00007', '.', 1, 2)
- INSTR ('XXXXXX.0001.09011.0001.00002.03.0004.0005.0006.00007', '.', 1)
- 1
) col2,
SUBSTR ('XXXXXX.0001.09011.0001.00002.03.0004.0005.0006.00007',
INSTR ('XXXXXX.0001.09011.0001.00002.03.0004.0005.0006.00007', '.', -1, 2) + 1,
INSTR ('XXXXXX.0001.09011.0001.00002.03.0004.0005.0006.00007', '.', -1, 1)
- INSTR ('XXXXXX.0001.09011.0001.00002.03.0004.0005.0006.00007', '.', -1, 2)
- 1
) col3
from dual
It is very urgent.
Thanks in advance.


npejavar wrote:
It is very urgent.
It doesn't look urgent, you could simply read the manuals for instr and substr or describe any issues or errors you are having, or post sample data so people could help you more easily, or format your code so it is more readable, but you don't bother to do any of those things so if it isn't important to you to extend any effort, why would it be important to us?
If it was really urgent it would be a violation of the conditions of use of these forums.
4. Use of Community Services
Community Services are provided as a convenience to users and Oracle is not obligated to provide any technical support for, or participate in, Community Services. While Community Services may include information regarding Oracle products and services, including information from Oracle employees, they are not an official customer support channel for Oracle.
You may use Community Services subject to the following: (a) Community Services may be used solely for your personal, informational, noncommercial purposes; (b) Content provided on or through Community Services may not be redistributed; and (c) personal data about other users may not be stored or collected except where expressly authorized by Oracle

Read other 6 answers


Related Articles

  • Breaking the string into different columnsNovember 30

    Hi Guys, I need to break the following string into different columns 'XXXXX.0001.09011.0001.00002.03.0004.0005.0006.00007.' I am trying to write it using instr and substr , but having some issues . Is there any other way to do this. If not can someon

  • How to Break this String and put into a TableNovember 30

    Hi all, Currently i working on the Reading of Csv file.THe Csv file wil be stored as BLOB in one table. The Format of the Csv file is EMPCODE :DATEOFBIRTH 312089 ,12/01/1984 321456 ,03/05/1980 120212 ,04/08/1987 312456 ,23/12/1977 311110 ,12/04/1998

  • In report layout, can format a long string display as Column mode?October 11

    Hi, Just want to check in report builder any way can format a long string into a column mode? Example: I have a field to hold 8000 character, and would like to print 30 character in each line, when reach 50 line, then print in column i/o to print nex

  • Warning - 1241024 - Possible string truncation in column #October 11

    I am running the following command in a simple MaxL script, the output of which is sent to a text file: display variable App.DB.SubVar; The text file appears to have generated fine, with the substitution variable value displayed. However, the followi

  • Turning sql  string (with dynamic columns) into a pipelined functionOctober 11

    Hi guys, I was working on an apex report the other day, and wrote the sql below (for those who don't know apex well, in an apex report you can define the columns at runtime.) When I was finished, I said to myself: "It would be great to have a pipelin

  • How to use string as a column nameOctober 11

    Hello, I have a fn getField() that returns a column delimited string - amt1,amt2 . I want to use this string as the column name in a second query on table MASTER_AMT and get the values for amt1 and amt2...so when i try.... select getField( 'NPC_NOYTG

  • How to Store Encrypted String into OracleDatabase Column(vacrchar2)November 30

    Hi.. I encountered an error while inserting a Encryted String into oracle column.. Error "Quoted String Doesn't terminated properly" But i wrote Query string correctly and using Datatypes as String and vacharchar2(2000) in java and oracle respec

  • How to insert a very long string into a column of datatype 'LONG'November 30

    Can anyone please tell me how can I insert a very long string into a column of datatype 'LONG'? I get the error, ORA-01704: string literal too long when I try to insert the value into the table. Since it is an old database, I cannot change the dataty

  • Error SQL70015: Error validating element [dbo].[GetDocument]: Deprecated feature 'String literals as column aliases' is not supportedNovember 30

    Using MS SQL Management Studio 2012 attempting to export (from GoDaddy) a .bacpac database for import to Azure and get the error message listed in the Title (String literals as column aliases is not supported on SQL Azure). There was a discussion of

  • Break a string like this 06-26-002-30-1 within a view?November 30

    Hello, I have been trying to do this for a while with no luck. Is there a way to break a string like this 06-26-002-30-1 within a view? Thank youBTW. What happened to "-26-"?I missed it. I post it again anyway Processing ... WITH t AS (      SEL

Copyright (C) 2019 wisumpire.com, All Rights Reserved. webmaster#wisumpire.com 14 q. 0.613 s.