Sorting Strings As Integers

Jun 10, 2007

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

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: Rollup And Sorting

Jul 2, 2012

I have the following query

SQL>
SQL> SELECT DECODE(act.statement_flag, NULL, 'Total',act.statement_flag) "Monthly Statement?",
2 COUNT(account_id) "Count",
3 ROUND(((COUNT(account_id)/
(SELECT COUNT(account_id) FROM accounts
WHERE account_status_id = consts$.acct_sts_active))* 100),2) || '%' "Percent of Active Accounts"
4 FROM accounts act
5 WHERE act.account_status_id = consts$.ACCT_STS_active
6 GROUP BY ROLLUP(act.statement_flag)
SQL> /

Monthly Statement? Count Percent of Active Accounts
------------------ ---------- -----------------------------------------
N 1892 98.08%
Y 37 1.92%
Total 1929 100%

I want the output to be in the following fashion:

Monthly Statement? Count Percent of Active Accounts
------------------ ---------- -----------------------------------------
Y 37 1.92%
N 1892 98.08%
Total 1929

And I would also like to know is there another way to find the Percent of Active accounts in the query.

View 3 Replies View Related

PL/SQL :: Sorting Of Character?

Feb 7, 2013

I got the following data for which I will like to sort in ascending order.

B51-01-008/04022013/CRE1005/1
B51-01-008/04022013/CRE1005/2
B51-01-008/15-1-13/SSA6280/1
B51-01-008/31012013/CRE1005/1
B51-01-008/31012013/CRE1005/10
B51-01-008/31012013/CRE1005/2

and expected result will be

B51-01-008/15-1-13/SSA6280/1
B51-01-008/31012013/CRE1005/1
B51-01-008/31012013/CRE1005/2
B51-01-008/31012013/CRE1005/10
B51-01-008/04022013/CRE1005/1
B51-01-008/04022013/CRE1005/2

I will like to sort it based on the order on second segment for which is date, the earlier appear first then only the rest.

View 11 Replies View Related

SQL & PL/SQL :: Sorting Hierarchy Query

Jun 10, 2013

Is it possible to sort records based on hierarchy in such a way that records sorts in sequence and records of same level comes in the last (in sorting order)

Eg: Default hierarchy level order: 0,1,2,2,3,3,3,4,5

Above given is the default sorting order of an query for an parent 'NODE_A1' having multiple same level i.e 2 and 3

Expected Hierarchy level order: 0,1,2,3,4,5,2,3,3

Please check attached file for an example.

View 8 Replies View Related

SQL & PL/SQL :: Sorting VARCHAR2 Field

Mar 23, 2010

sort data set A,B,1,2,A1,A2,B1,B2,2B,1000 as A,B,1,2,1000,A1,A2,B1,B2,2B.

I tried with LPAD and EXPREG_REPLACE funtion. But it did not work.

View 13 Replies View Related

SQL & PL/SQL :: Alphanumeric Sorting Column Base

Jun 18, 2010

I am trying to sort columns base on cluster and alpha numeric field.

Column1 Columns2 column3
1 2-CA6R-234 9
1 2-CA6R-231 8
1 ARCT-0037000000ewegZ 10
2 2-QIZFF7 1
3 2-PIZFF6 6
3 ARCT-0037000000ewipk 9
3 2-QIZTF7 1

Wanted to sort in a way that column1 will be same order and the second column will order first with ARCT-XXXXX and then reset of the column2. It should look like this

Column1 Columns2 column3
1 ARCT-0037000000ewegZ 10
1 2-CA6R-231 8
1 2-CA6R-234 9
2 2-QIZFF7 1
3 ARCT-0037000000ewipk 9
3 2-PIZFF6 6
3 2-QIZTF7 1

View 9 Replies View Related

SQL & PL/SQL :: Sorting Collection In Nested Table

Sep 11, 2012

I'm trying to sort a collection in a nested table in PL/SQL so these value can be used later for a display and for export to Excel. The sort is failing.

-- PLS-00642: local collection not allowed in SQL statements

-- PL/SQl: ORA-00902: invalid datatype

The error message are also noted below in the code on the line that fails.

A quick overview of this code- Using a nested table MyNestedTable the values from several select queries are combined into MyRecordsetZero using CURSOR, and MULTISET UNION. I'm trying to either sort MyRecordsetZero or populate MyRecordsetSorted with the sorted values for futher use.

IMPORTANT: The code is running in an enviroment that does not have permission to create.

Versions:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Developer: 8.0.0.1480

DECLARE
-- Declare the variable MyTID to be used by select statements and
-- set its data type and max character count
MyTID varchar2(10);
[code]....

View 13 Replies View Related

SQL & PL/SQL :: Sorting Nested Table / Collection

Nov 10, 2011

In test.pks file I declared the following type

TYPE tab_tests is table of NUMBER(15);

In test.pkb, I have the following procedure

PROCEDURE report (
i_cid IN NUMBER
)
IS
test1 tab_tests := tab_tests();
test2 tab_tests;
BEGIN

-- I populate test1 with the data and it works fine. But when I tried to make a sorted nested table with the following command.

I got this error 'ORA-00902: invalid datatype',

a nested table than the following, like a built in Oracle collection method?

execute immediate 'select cast( multiset (select * from table(test1) order by 1) as tab_tests) INTO test2 FROM dual';
END;

View 16 Replies View Related

Reports & Discoverer :: Sorting On Different Columns

Sep 10, 2012

I have report where there are multiple columns and requirement is user may choose his own sorting order from form, any sample .rdf.

CREATE TABLE EMP (EMP_CODE VARCHAR2(12),EMP_NAME VARCHAR2(20),SAL NUMBER);
INSERT INTO EMP VALUES ('1','A',10);
INSERT INTO EMP VALUES ('2','C',3);
INSERT INTO EMP VALUES ('3','B',20);

[Code]..

--note order by is changing, how its doable in report 6i.

View 2 Replies View Related

Reports & Discoverer :: Sorting Order

May 18, 2011

1) eno list values ASC , DESC
priority Column : 2

2) DNO List Values ASC , DESC
priority column : 1

3) DTNO List Values ASC , DESC
priority column : 3

So i need the sorting order output like this
LIST VALUES ASC (OR) DESC USER SELECT AT RUN TIME ANY VALUE.

ORDER BY DNO DESC , ENO ASC , DTNO ASC

* The Priority is user will enter at run time based on the number the column should be sorting.

View 6 Replies View Related

PL/SQL :: Simple Character Sorting Of Results

Jun 8, 2012

I have three SQLS which I am running using "Union"

e.g

select col1,count(*) from table where type ='parts'
group by col1
union
select col1,count(*) from table where type ='consumables'
group by col1
union
select 'Total',count(*) from table
group by 'Total'

I want to force the total column to be the last row of the select and the parts column to be the first row always. I did try something like prefixing the col1 with an alphabet. But that shows up in the output also. Was looking for a better way!

View 5 Replies View Related

Forms :: Sorting Values In List Item

Dec 15, 2010

I have two list items and from left list item values are populated to right list item through Add and Remove buttons and vice versa.My requirement is

1.) I need to sort the values of list item whenever a new element is added to the list item.

View 1 Replies View Related

Reports & Discoverer :: Selective Sorting On Report Group?

Dec 11, 2010

I have a Group report based on one group.For eg..there is machine (Group) and it has detail records .The problem is there are certain specific machine on which i want sorting to be done the rest will be not sorted.

machine WX
Details not be sorted
machine sh4
Details to be sorted
machine sh5
Details to be sorted
machine AN1
Details not to be sorted

View 12 Replies View Related

Reports & Discoverer :: Sorting Data And Hidden Frames

Dec 15, 2011

Jow can i sort field data getting from formula column. The filed that i want to sort have source of that formula column. When i use order by clause with :abc ---(formula column) then it doesn't not work.

View 3 Replies View Related

Application Express :: Combined Date Sorting With Anychart

Apr 25, 2013

I have a table in which I extract the year and the month and both in combination (from a date type). This is needed to summarize for example the year in a chart.

I need the combination of month and year because people could do a query which switches through years. For expample: From 02.2012 to 03.2013.My problem is I dont know how to tell anychart to sort the dates. I have already tried:

to_number(to_char(testdate,'YYYY')) as Year,
to_number(to_char(testdate,'YYYY'))||to_number(to_char(testdate,'MM')) as MonthyearApex now sorts:

20121,201212,20122

I would like that apex realizes to order:

1.2012,2.2012,3.2012 .........01.2013,02.2013.03.2013

The report itself has now four date relating collums:

1) Date (Type Date)
2) Year (Type Number)
3) Month (Type Number)
4) Monthyear (Type varchar)

View 3 Replies View Related

SQL & PL/SQL :: To Compare Two Strings

Apr 8, 2008

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

View 6 Replies View Related

SQL & PL/SQL :: String Between Two Strings?

Apr 4, 2012

I 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');

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

Reports & Discoverer :: Oracle Report 10g - Sorting In Break Order

Nov 30, 2010

Is there a way to short a Matrix report? The query shorts correctly in PLSQL but not in the generated report file.

There are columns like Date,Device,Operator Id etc and the report should be able to short depending on the requirement. For example,

it should be able to short by device, or by Date or both. I tried sorting in the 'Break order' but doesn't come out as required.

View 6 Replies View Related

Trimming Strings - Comparing?

Jul 26, 2007

I have 2 strings that I want to compare for example

string1 = 'ABC~AB/10/1234'
string2 = 'ABC~AB/10/1234~.....'

There could be anything after the 2nd ~ in string 2 is there a easy way of trimming string2 to the first 14 Characters? Or do I have to find the 2nd instance of ~ and then remove everything after (and including) that?

View 2 Replies View Related

SQL & PL/SQL :: How To Extract Substring From A Set Of Strings

Jan 25, 2012

Im trying to extract, *THIS IS MY STRING* from *<YUVRAJ THIS IS MY SRTING YUVRAJ>* .

In this <YUVRAJ and YUVRAJ> is constant, need to remove which is being appended at begin and end for a set of strings.

View 3 Replies View Related

PL/SQL :: Embedding Quotes In Strings?

Aug 16, 2013

In the code segment below (hope it appears right) I can understand the use of single quotes in the first two examples but in the third example below I had to use double quotes around the word - Today's - and I not sure I understand why?! I'm aware of the rules ...If you want a single quote to appear in the middle of a string add another single quote to it.If you want a single quote to appear at the beginning or end of a string add 2 single quotes to it.If you want a single quote to appear on its own add 3 single quotes to it. 

SQL> select 'This isn''t' from dual;
'THISISN''
----------
This isn't
SQL> select to_number('34@456#789', '999G999D999', 'nls_numeric_characters=''#@'' ') from dual;
TO_NUMBER('34@456#789','999G999D999','NLS_NUMERIC_CHARACTERS=''#@''')
---------------------------------------------------------------------
                                                            34456.789
SQL> select to_char(sysdate, 'fm"Today''s" ddth Month YYYY') from dual;
TO_CHAR(SYSDATE,'FM"TODAY''S"DDTHMONTHYYYY')
------------------------------------------------------
Today's 16th August 2013

View 12 Replies View Related

PL/SQL :: Extract Values Of Strings?

Jun 20, 2012

extract the value of the strings for REQUEST_GROUP_CODE and REQUEST_GROUP_APPL_SHORT_NAME. As you can see I have to deal with whitespace differences, case differences etc. I need the values between the quotes for each one. get ACCOUNTING and AR for the first example. I am using db version 11g r1.

WITH TEST AS
(SELECT 'REQUEST_GROUP_CODE="ACCOUNTING" REQUEST_GROUP_APPL_SHORT_NAME="AR" TITLE="AR:AR_SRS_TITLE_ACC_RPTS"' str FROM dual
UNION ALL
SELECT 'REQUEST_GROUP_CODE = "PRINT_CONSINV"REQUEST_GROUP_APPL_SHORT_NAME = "AR"TITLE =

[code]...

View 5 Replies View Related

SQL & PL/SQL :: Comparing Strings Entered In A Form?

May 27, 2011

I have to compare a string entered in a form with a series of English strings in back-end PL/SQL( using LIKE operator )

But it so happens, the string entered in the form is specific to the language used in the country. for ex. in Dutch, it is entered in Dutch language.

So on comparing, it fails as the PL/SQL compares it with English Strings.

View 4 Replies View Related

SQL & PL/SQL :: Splitting Given String Into Multiple Sub-strings?

Feb 18, 2013

I need to split the given string into muliple sub strings based on one special character

Ex : Speace is Special character

with data as (
select 'ab cd ef gh ' from dual )
select * from data

Required Output :
ab
cd
ef
gh

View 3 Replies View Related

SQL & PL/SQL :: Processing Comma Separated Strings

Mar 3, 2003

Outside of convoluted loop using the SUBSTR() function, is there an easy way to extract each element from a comma-sepearted list that's passed in to a stored proc?

View 4 Replies View Related

SQL & PL/SQL :: External Table And Blank Strings

Sep 23, 2011

I am importing some data using an external table, but the file on which the external table is built has some rows where a certain column is populated with two empty space characters.

CREATE OR REPLACE DIRECTORY xtern_data_dir AS 'C:/...';

CREATE TABLE ET_RPDMMA1_PEDI_MSTR (
GCN_SEQNO NUMBER(6),
PDM_MNAGE NUMBER(4),
PDM_MXAGE NUMBER(4),
PDM_MND NUMBER(18,6),
[code].......

This is an except of what's in the external text file. The full text file has been attached.

000011|0030|....|000000.000000| |000000.000000| |0002
000011|0365|....|000000.000000| |000000.000000| |0002
000011|0730|....|000000.000000| |000000.000000| |0002
^
blank spaces may be |
causing error----------

Here is the error message I am receiving. I believe this is caused by the blank fields in the data.

INSERT INTO RPDMMA1_PEDI_MSTR (GCN_SEQNO,....
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52

This is difficult to work with because the external table function does not appear to be even reading the file so it's not like I can convert the data as I'm loading into the internal database table. What are some approaches I can use to get Oracle to accept these blank columns and either populate them with blank spaces or set them to null?

View 12 Replies View Related

SQL & PL/SQL :: Insert Query Having Strings As A Column

Dec 13, 2012

tyring to insert an insery query having string as a column where i am supposed to insert a single quote casuing the problem.

insert into abc(x,y) values (1,'select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh='self' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij')

getting missing comma with the above query.when i tried to give as

insert into abc(x,y) values (1,'select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh=''self'' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij')

insert is happening but saving as "select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh=''self'' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij"

how to avoid this and get the select query to store as

select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh=''self'' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij

View 5 Replies View Related

SQL & PL/SQL :: Convert One String Into Multiple Strings?

Aug 31, 2010

i have a column 'name' in which value is 'Shailesh Negi',i have to insert 'shailesh' into'first name' column and 'Negi' into 'last name' column respectively.

View 20 Replies View Related

SQL & PL/SQL :: Updating Another Table For Equal Strings

Jun 8, 2010

There are two tables:

create table songs(song_name text, song_artist text,song_url text, song_cat text, last_edit text);
create table categories(cat_name text, cat_total int);

im trying to create a trigger that, when i insert a new song in the songs table, it will check the category of the song (song_cat) and increase the respective cat_total (from table categories) by 1.

here is what i've done so far:

drop trigger countcat;
CREATE TRIGGER countcat AFTER INSERT ON songs FOR EACH ROW
update categories SET cat_total= cat_total +1
WHERE cat_name = (select song_cat FROM inserted);

What to write in the cat_name = (select ...). I have tried lots of stuff but still nothing. when i use this, i get the error that mydatabase.inserted doesnt exist

View 4 Replies View Related







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