SQL & PL/SQL :: Sort Alphanumeric Data
Nov 20, 2008
I have an urgent request which is pending with the following problem.
Problem :
I have a table which contains data of various datatypes like alphanumeric,varchar and number.
Now my query is " how to sort the data of the table using alphanumeric field"
How to select the data in a required(MyRequirement) sort order.
Data
============
12.4PI1
12.4
12.2
12.4T
12.3PI1
[Code]....
afterSorting(which I am getting Now)
============
12.2
12.3PI1
12.4
12.4PI1
12.4PI10
12.4PI11
[Code]...
MyRequirement
===============
12.2
12.3PI1
12.4
12.4PI1
12.4PI2
12.4PI3
12.4PI10
[Code]..
Means it has to sort the data order by lefthand side of PI and also righthand side of PI.
Pls check the attachment if you are not getting the above data in correct order.
View 22 Replies
ADVERTISEMENT
Nov 23, 2008
I have the following three tables:
Buyer:
BuyerID
Name
Trans:
TransID
BuyerID
Trans_Item:
Qty
Price
BuyerID
TransID
I need to figure what buyer has bought the most things. I have a function already determines the amount each buyer has bought.
So that is done. I need to order this by buyerid. How do I sort something like that? ORDER BY and GROUP BY do not work.
View 2 Replies
View Related
Nov 15, 2012
How can i sort data in the execute Query mode during runtime,i have a columns which has number, char and date field.
This below data is fetched in execute query from a table;
x1 x2 x3
1343 adfa 11/14/2012
1353 adfa 11/11/2012
1333 adfa 11/10/2012
1333 adfa 11/12/2012
1353 adfa 11/09/2012
now i like to sort like this below;
x1 x2 x3
1343 adfa 11/09/2012
1353 adfa 11/10/2012
1333 adfa 11/11/2012
1333 adfa 11/12/2012
1353 adfa 11/13/2012
how to do it?
View 4 Replies
View Related
Aug 1, 2012
create table x(
sno varchar2(5)
);
insert into x values('A-1');
insert into x values('B-1');
insert into x values('B-2');
insert into x values('B-3');
insert into x values('1');
insert into x values('A-2');
insert into x values('2');
insert into x values('3');
insert into x values('A-4');
insert into x values('B-4');
insert into x values('C-4');
insert into x values('D-4');
SQL>select * from x;
SNO
-----
A-1
B-1
1
A-2
2
3
A-4
B-4
C-4
D-4
B-2
B-3
How can I select it ike this
1
A-1
A-2
A-4
B-1
2
B-2
3
B-3
B-4
C-4
D-4
View 15 Replies
View Related
Sep 8, 2011
i need a function which checks if v_rand carrying a value is alphanumeric if nt this value of 6 alpha numeric characters must be generated again... here is the actual fn.
Function alphanumeric
Return varchar2
is
v_rand varchar2(10) := 0;
Begin
[code]......
View 4 Replies
View Related
Apr 12, 2010
Is there a function or a process to generate and maintain an alphanumeric sequence?
I need to be able to sequentially generate both a 3 and 5 byte case sensitive alphanumeric primary key that uses A-Za-z0-9 characters intermittently of which the 5 byte MAY be a subset of the 3 byte. e.g. the key can be 00000 or a3BD7.
so I need to be able to generate a new 5 char using 3 char base and if that value is not available, generate a unique 5 char value and allocate the substr(1,3) of that value backwards.
Ex1: unique customer location comes in - not a preexisting customer name (same name,different location address doesn't exist) --> select nextval.[5char] into v_long_c to insert into customer_loc table, and substr (v_long_c, 1, 3) for customer name table.
EX 2: unique customer location comes in. customer name exists in customer table with 3char val A39, but that location doesn't exist in customer_location table (5chars are location specific).
I need to take the A39 and generate new 5char for that unique location using that prefix (A39) if possible.
However, a number of a39-- already exist, though not all assigned to the same customer name (we're trying to keep them grouped together but that might not be logically feasible)
How do I select next a39||[2char] for that unique location - and if that value is not available (all 62*62 possibilities have been used for A39--), select nextval.[5charseq] into long_key.
I suspect someone out there knows the functions I can use to create this or has written a package to do just this; I suspect with enough time I could do it, but I don't currently have the time or knowledge to develop it within these deadlines. I thought it would be easier.
My short-sighted solution was to create a static table of all iterations of A-z0-9 5 char values, select one, mark it used and move on. Unfortunately 62^5 is a substantial number (913+million records) and that table took a LOT of space, causing my development server to groan and crack miserably. indexing on it takes a lot of space too (and trying to build multiple indexes exceeded database size). But without an index on the 3char field, selecting an available 5 code from it based on the customer_3char prefix took five minutes - much longer than the fraction of a second I need.
View 5 Replies
View Related
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
Oct 16, 2012
I have a column COL1 in table TAB1 which is varchar2. I want select only rows which has number and not alphanumeric value? I don't want to use regexp for this since
View 11 Replies
View Related
Feb 8, 2011
How can I find the last numeric value in the alphanumeric string?
Example:
LS14160220SPAD show me 0
MN23160224N show me 4
SP34524442 show me 2
View 9 Replies
View Related
Feb 14, 2011
To built the Pl/SQL function or SQL query to validate the given string for alphanumeric character.
I would pass the string of size 10 character, we need to validate first 5 character as alphabet, next 4 character as numeric and last 1 character as alphabet.
I will pass the each row value to the function, it need to return "T" or "F" based on the condition,first 5 character as alphabet, next 4 character as numeric and last 1 character as alphabet.
Here is the DML and DDL.
[code]
create table abc ( classid varchar(10));
insert into abc values ("abcde1234f");
insert into abc values ("abcde12345");
insert into abc values ("ab1de1234f");
insert into abc values ("abcde1234f");
insert into abc values ("abcd21234f");
[code]
Output:
Input : abcde1234f Output : T
Input : abcde12345 Output : F
Input : ab1de1234f Output : F
Input : abcde1234f Output : T
Input : abcd21234f Output : F
View 6 Replies
View Related
Dec 27, 2012
I'm a newbie to PL/SQL. I had a quick query about trapping exceptions.
I have a sample table called my_emp, which contains last name, salary, etc. I have written the following code that takes in an employee salary and if the salary exists it displays the last name and corresponding salary. If two or more rows are returned, the exception handles it. Likewise if there are no records with that salary, the exception takes care of it.
I was trying to input an alphanumeric input, such as 1bbb as the salary and of course ORA-06502 error pops up in the sql command line. I now want to trap this using an exception but whatever I try I still get the ORA-06502 in the calling environment rather than getting the 'Not a number' or 'Some other error occured' message. why the WHEN VALUE_ERROR or the WHEN OTHERS exceptions are not trapping the error?
DECLARE
v_sal NUMBER (12) := '&Enter_salary';
v_last_name VARCHAR2(10);
BEGIN
SELECT last_name
[code]...
View 3 Replies
View Related
Nov 12, 2012
I have created a form that have a button to check the character in the textbox is alphanumeric and symbol. Here attached the code, that i tried from other post.
View 5 Replies
View Related
Mar 10, 2010
I am running this query but am not getting data that is correct.
SELECT a.prod_id, a.prod_name, a.artist_name, COUNT(*)
FROM po_my_purchase_tb a, cm_track_tb b
WHERE a.prod_id = b.prod_id and b.GNR_CD = 'GR000017' AND a.purchase_date > '10-FEB-10' AND ROWNUM<50
GROUP BY a.prod_id, a.prod_name, a.artist_name, a.buy_seq
ORDER BY COUNT(*) desc
View 8 Replies
View Related
Jan 20, 2012
Suppose I have a table in which I have first_name, last_name, dob. Now I have to fetch on the basis of first_name=some_value, last_name=some_value and dob=some_date. I want to sort it on the basis of exactly fetched values. Let me take an example-
test table contains-
first_name last_name dob
---------- --------- ----
Manu Batham 02-Feb-1988
Manu Sharma 01-Jul-1987
Avinash Pandey 03-Feb-1988
Ankit Gupta 02-Feb-1988
Manu Aggrawal 02-Feb-1988
Manu Batham 20-Jan-1985
Sikha Batham 17-Apr-1988
Now if I give parameters-
first_name='Manu'
last_name='Batham'
dob='02-Feb-1988'
then my result should be like below-
result-
first_name last_name dob
---------- --------- ----
Manu Batham 02-Feb-1988
Manu Aggrawal 02-Feb-1988
Manu Batham 20-Jan-1985
Manu Sharma 01-Jul-1987
Ankit Gupta 02-Feb-1988
Sikha Batham 17-Apr-1988
My result is based on the approach-
if matched first_name, last_name, dob --> 1st prefrence in order
if matched first_name, dob --> 2nd prefrence in order
if matched first_name, last_name --> 3rd prefrence in order
if matched last_name, dob --> 4th prefrence in order
if matched first_name --> 5th prefrence in order
if matched last_name --> 6th prefrence in order
if matched dob --> 7th prefrence in order
I designed the following query for the same-
Select first_name,last_name,dob,1 "Order" from test Where
first_name='Manu' and
last_name='Batham' and
dob=to_date('02/02/1988','dd/mm/yyyy')
union
Select a,b,c,2 from test Where
[code]......
I know that this is not the best possible solution as the table is very big and doing so many hits on that table will certainly decrease the performance.
View 19 Replies
View Related
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
Jun 16, 2010
how does sorting on multiple columns work
suppose my query is
select * from person order by first_name desc
and sys_person_id asc
this query works , but is this write way to sort on multiple column ?
View 12 Replies
View Related
Jun 22, 2011
I want a trigger i have made a software abut school system i need a trigger to sort out the positions...if total number like 100,99,98 than in positions column 1,2,3 but if total marks same like 100,100 in position column shows 1,2..i need if the marks are same than in position column also same like if marks 100,100 in position column shows 1,1
View 3 Replies
View Related
Oct 17, 2012
Query result gives out put like in the following order
CITY NAME
CHENNAI
DELHI
LONDON
RIO DE GENARO
How to get output in the following sort order
LONDON
CHENNAI
DELHI
RIO DE GENARO
i dont want to hardcode the column values ..it will be great if i get option to generate dynamically.
View 9 Replies
View Related
Jul 16, 2010
I know the only way to guarantee a specific sort order result when querying table is by using the order by clause. However, I have an issue where I do not have access to the code for the web user interface of a very lightly used interface (has two users). In that user interface is a drop down box that is populated by a table - one table. The drop down box is populated by the query "select [column name] from [table name]". Right now there are 400+ rows in that table total, so it's small but not having the items ordered is a pain.
I would like to alter something on the db side so the result of "select [column name] from [table name]" is an ordering by the column descending. I don't want the sort order to be the same for all queries (including joins and all) just want to control the order for that one query. It is Oracle9i.
View 1 Replies
View Related
Jan 26, 2011
I have a master detail form that presents a list of 22 items that can be checked or unchecked depending on whether the test is needed.
The issue I have is trying to order or sequence the list in a specific order without a database column.
Here is the code I am trying to use in a post query trigger:
CASE :SAMPLE_TESTS.TESTCODE
when 'L001'
then :SAMPLE_TESTS.SEQ := '02' || :SAMPLE_TESTS.TESTCODE;
when 'L002'
then :SAMPLE_TESTS.SEQ := '03' || :SAMPLE_TESTS.TESTCODE;
when 'L003'
then :SAMPLE_TESTS.SEQ := '04' || :SAMPLE_TESTS.TESTCODE;
[Code]..
The non-database field is character with a length of 6.
When I try to use this field in the order by property of the data block it is unable to perform query.
Is there another way?
View 1 Replies
View Related
Jan 10, 2012
i got the data like
select * from Table1
SNO Name B_MONTH
--------------------
101 A Mar
102 B Jan
103 C Feb
104 D Apr
105 f May
106 G Jun
Select * from Table2
107 H Dec
108 I Aug
109 J Oct
110 L Jul
111 M Sep
112 N Nov
select * from table1 union select * from table2 order by 3
The B_MONTH column is in Varchar2. Expected output should be
Output:
Jan
Feb
Mar
Apr
.
.
.
.
Nov
Dec
View 8 Replies
View Related
Dec 12, 2010
I have a name field like below; i need a query to display the name field in alphabetical order like in dictionary.
Emp Name
--------
Sam
John
Noel
Alen
Saaem
output would be
---------------
Alen
John
Noel
Saaem
Sam
View 5 Replies
View Related
Jun 12, 2012
I tried to search on google for "Hash Join" And "Sort Merge". But unfortunatly i am unable to understand that articles. "Hash join" And "Sort Merge".
View 3 Replies
View Related
Jan 14, 2013
I need to sort values inside a column. Data in column is concat with '|' (pipe). There could be 1 to many values in one column.
The order of the rows is not relevant here.
Test case:
-------------
create table t1 (col1 varchar2(100));
insert into t1 values ('H302|H411|H317|H314|H312');
insert into t1 values ('H315|H410|H400|H318');
insert into t1 values ('H226|H400|H331|H318|H317|H315|H310|H301');
insert into t1 values ('H301');
commit;
select col1 from t1;
COL1
----------------------------------------
H302|H411|H317|H314|H312
H315|H410|H400|H318
H315|H318
H301
H226|H400|H331|H318|H317|H315|H310|H301
Need is this:
COL1
----------------------------------------
H302|H312|H314|H317|H411
H315|H318|H400|H410
H315|H318
H301
H226|H301|H310|H315|H317|H318|H331|H400
View 4 Replies
View Related
Jul 7, 2012
am using a scalar subquery in order to sort by department_name column. My sql statement is like this:
select employee_id,last_name
from employees e
order by
(select department_name
from departments d
where d.department_id= e.department_id)
i want to display also the department_name in my output.
select employee_id,last_name,
(select department_name
from departments d
where d.department_id= e.department_id) department_name,department_id
[Code]...
the result is sorted and name is also displaying.but am not getting the output properly.
result is like this:
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME DEPARTMENT_ID
--------------------------------------------------------------------------------------------------------
205 Higgins Accounting 110
206 Gietz Accounting 110
200 Whalen Administration 10
107 Lorentz IT 60
105 Austin IT 60
103 Hunold IT 60
104 Ernst IT 60
106 Pataballa IT 60
am getting the first three line.but as 5 worker are in IT department so in that case how result is sorted..
View 1 Replies
View Related
Oct 1, 2013
I am using oracle database 11g.My use case is I do have a table with following valuesTable name -test
product id productsortdescription
H58098 ACETAMIDOHYDROXYPHENYLTHIAZOLE
043994 Alloy .MM.INTHICK My query is
select * from test order by productsortdescription; this query gives result as is like
product id productsortdescription
H58098 ACETA
product id productsortdescription
H58098 ACETAMIDOHYDROXYPHENYLTHIAZOLE
043994 Alloy .MM.INTHICK
MIDOHYDROXYPHENYLTHIAZOLE
043994 Alloy .MM.INTHICK
but Expected output/result should be like below:
product id productsortdescription
043994 Alloy .MM.INTHICKH58098 ACETAMIDOHYDROXYPHENYLTHIAZOLE as All and ACE
in productsortdescriptionl is in small case than C. NLS Session parameters are as following
SELECT * from NLS_SESSION_PARAMETERS;
NLS_LANGUAGE AMERICANNLS_TERRITORY AMERICANLS_CURRENCY $NLS_ISO_CURRENCY AMERICANLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIANNLS_DATE_FORMAT DD-MON-RRNLS_DATE_LANGUAGE AMERICANNLS_SORT BINARYNLS_TIME_FORMAT HH.MI.SSXFF AMNLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AMNLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZRNLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZRNLS_DUAL_CURRENCY $NLS_COMP BINARYNLS_LENGTH_SEMANTICS BYTENLS_NCHAR_CONV_EXCP FALSE
View 5 Replies
View Related
Aug 4, 2008
I have a requirement to sort a comma seperated string. For example if I pass '1234,432,123,45322,56786' as string, then it should return '123,432,1234,45322,56786', after sorting the numbers inside the string.
I have done it creating Global Temporary table. Is there a way without creating the Temp table. I understand I can write the whole logic to sort and append the string, but if there is any direct way.
CREATE GLOBAL TEMPORARY TABLE TEMP_TAB(COL1 VARCHAR2(100)) ON COMMIT DELETE ROWS;
CREATE OR REPLACE FUNCTION func_sort_string(pi_string IN VARCHAR2, pi_delimiter IN VARCHAR2 DEFAULT ',')
RETURN VARCHAR2 IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_str VARCHAR2(2000) DEFAULT pi_string || ',';
[code]...
View 26 Replies
View Related
Jun 4, 2010
I've done this once before, but can't seem to find the sql.
How can I sort by month and year on a column called ex: TEST_dATE
JAN 2007
FEB 2007
APR 2008
SEP 2009
OCT 2009
FEB 2010
JUN 2010
View 15 Replies
View Related
Jun 12, 2012
Haw to sort the data based on financial year.
For example Indain Fiscal year 01-Apr-2010 to 31-Mar-2011
Apr2010 -1
May2010 -2
.
.
.
.
Feb2011-11
Mar2011-12
I had given a query..for Quarter idendification
SELECT distinct
'Qtr'
|| CASE
WHEN PERIOD BETWEEN TO_DATE ('01-04-2010', 'DD-MM-YYYY')
AND ADD_MONTHS (TO_DATE ('01-04-2010', 'DD-MM-YYYY')-1, 3)
[code].....
View 2 Replies
View Related
May 6, 2010
I am maintenancing a form which I have to add buttons as headers that will sort each column's data either in desc or asc order when the user click each button. How is this done? I need to know what built-in function that will closely do this or cod.
View 19 Replies
View Related
