Home » SQL & PL/SQL » SQL & PL/SQL » display all numbers with like (11g)
display all numbers with like [message #658836] Wed, 28 December 2016 04:51 Go to next message
glmjoy
Messages: 187
Registered: September 2011
Location: KR
Senior Member

select CATEGORY_CODE, CATEGORY_Name
from CATEGORY
where cancel_yn <> 'Y'
AND To_Char(To_Number(CATEGORY_CODE)) LIKE '%'
order by 1 ASC

-----------------
My Result is

CATEGORY_CODE CATEGORY_NAME
------------- --------------------------------------------------------------------------------------
5 VEHICLE
6 FURNITURE & FIXTURE
7 COMPUTER ACCESSORIES / LAPTOP
8 OFFICE EQUIPMENT
9 GENERATORS
10 MOBILE SET
11 AC SPLIT

7 rows selected.


But I want to display all numbers.

---------------------

CREATE TABLE CATEGORY
(
CATEGORY_CODE NUMBER(5) NOT NULL,
CATEGORY_NAME VARCHAR2(100 BYTE) NOT NULL,
CANCEL_YN CHAR(1 BYTE) DEFAULT 'N' NOT NULL,
)


Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(5, 'VEHICLE', 'N');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(6, 'FURNITURE & FIXTURE', 'N');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(7, 'COMPUTER ACCESSORIES / LAPTOP', 'N');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(8, 'OFFICE EQUIPMENT', 'N');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(9, 'GENERATORS', 'N');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(10, 'MOBILE SET', 'N');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(11, 'AC SPLIT', 'N');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(12, 'LAPTOP', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(13, 'P.C', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(14, 'LCD', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(15, 'MONITOR', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(16, 'UPS', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(17, 'EXHAUST FAN', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(18, 'WHITE BOARD', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(19, 'BLACK BERRY', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(20, 'PRINTER', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(21, 'TP LINK ROUTER', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(22, 'HAND DRYER', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(23, 'INTERCOM', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(24, 'CORDLESS', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(25, 'CAMERA', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(26, 'BRACKET FAN', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(27, 'CHAIR', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(28, 'TABLE', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(29, 'CABINET', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(30, 'SOFA SET', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(31, 'FIRE EXTINGUISHER', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(32, 'SECURITY CAMERA', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(33, 'MICROWAVE OVEN', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(34, 'REFRIGERATOR', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(35, 'WATER DISPENSER', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(36, 'TEA MAKER', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(37, 'SCANNER', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(38, 'STABILIZER', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(39, 'CELLING FAN', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(40, 'WEB CAM', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(41, 'PENA FLEX', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(42, 'SIGN BOARD', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(43, 'INTERNET USB', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(44, 'SIDE TABLE', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(45, 'REVOLVING CHAIR', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(46, 'MATTREES', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(47, 'CUPBOARD', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(48, 'CARPET', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(49, 'FILE CABINET', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(50, 'BATTERY', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(51, 'WATER PUMP', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(52, 'EXTERNAL DVD', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(53, 'TB WD', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(54, 'NOTICE BOARD', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(55, 'CATTEL', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(56, 'SOIL SAMPLER', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(57, 'CONDUCTIVITY METER', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(58, 'PORT D-LINK SWITCH', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values
(59, 'SERVER', 'Y');
Insert into CATEGORY
(CATEGORY_CODE, CATEGORY_NAME, CANCEL_YN)
Values (60, 'DESK', 'Y');
COMMIT;

Re: display all numbers with like [message #658838 is a reply to message #658836] Wed, 28 December 2016 05:18 Go to previous messageGo to next message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
You filter out a lot of items because of cancel_yn <> 'Y'
Why do you convert number to number? CATEGORY_CODE is already a number
Re: display all numbers with like [message #658839 is a reply to message #658838] Wed, 28 December 2016 05:32 Go to previous messageGo to next message
glmjoy
Messages: 187
Registered: September 2011
Location: KR
Senior Member
Thanks a lot I got it
Re: display all numbers with like [message #658844 is a reply to message #658839] Wed, 28 December 2016 07:50 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
What is the point of

AND To_Char(To_Number(CATEGORY_CODE)) LIKE '%'

It's the same as not having the condition at all
Re: display all numbers with like [message #658847 is a reply to message #658844] Wed, 28 December 2016 08:12 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Except that the expression can raise an error. Laughing

Previous Topic: ROLLBACK usingTRUNCATE
Next Topic: Oracle sql id count
Goto Forum:
  


Current Time: Fri Mar 29 04:08:05 CDT 2024