Hi I have a requirement
like below
my oracle version
==============
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2.0.4.0 Production"
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
sample data creation
================
REATE TABLE test (
id NUMBER(4),
keystg VARCHAR2(1000)
);
REM INSERTING into EXPORT_TABLE
SET DEFINE OFF;
Insert into test (keystg,id) values ('1.1 1.1 1.1 1.1 tesddddt ',1090);
Insert into test (keystg,id) values ('1.2 1.2 1.2 1.2 ddfddddd111f v',1124);
Insert into test (keystg,id) values ('1.3 1.3 1.3 1.3 tes1111t ',1125);
Insert into test (keystg,id) values ('1.4 1.4 1.4 1.4 tesdddfft ',1126);
Insert into test (keystg,id) values ('1.5 1.5 1.5 1.5 te2323st ',1127);
Insert into test (keystg,id) values ('1.6 1.6 1.6 1.6 dtedfdfd123st',1128);
Insert into test (keystg,id) values ('1.7 1.7 1.7 1.7 stset2345 ',1129);
Insert into test (keystg,id) values ('1.8 1.8 1.8 1.8 sf 9094444',1130);
Insert into test (keystg,id) values ('1.9 1.9 1.9 1.9 dfdsf',1131);
Insert into test (keystg,id) values ('1.10 1.10 1.10 teeee NB 911',1151);
Insert into test (keystg,id) values ('1.11 1.11 ter1234 inr testere',1152);
Query I am using to get output
=======================
SELECT
id,keystg,
trim(regexp_replace(keystg, '[0-9\. ,]+',''))
FROM
test
WHERE
id in(1090,1129,1127,1152,1151);
but this is removing all numbers i dont want the numbers in string to be removed.
expected output is
===============
ID KEYSTG RES
---------- ---------------------------------------- ------------------------------
1090 1.1 1.1 1.1 1.1 tesddddt tesddddt
1124 1.2 1.2 1.2 1.2 ddfddddd111f v ddfddddd111f v
1125 1.3 1.3 1.3 1.3 tes1111t tes1111t
1126 1.4 1.4 1.4 1.4 tesdddfft tesdddfft
1127 1.5 1.5 1.5 1.5 te2323st te2323st
1128 1.6 1.6 1.6 1.6 dtedfdfd123st dtedfdfd123st
1129 1.7 1.7 1.7 1.7 stset2345 stset2345
1130 1.8 1.8 1.8 1.8 sf 9094444 sf 9094444
1131 1.9 1.9 1.9 1.9 dfdsf dfdsf
1151 1.10 1.10 1.10 teeee NB 911 teeee NB 911
1152 1.11 1.11 ter1234 inr testere ter1234 inr testere
please advise
thanks.