Home » SQL & PL/SQL » SQL & PL/SQL » regexp_replace (merged 2)
regexp_replace (merged 2) [message #679374] Wed, 26 February 2020 14:25 Go to next message
akarra
Messages: 24
Registered: August 2011
Location: Housston
Junior Member
Hi I have a requirement
like below


create 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);


commit;


select id,REGEXP_REPLACE(keystg,':digit:'),keystg from test

when i see the output it is removing all number including numbers in string.

coming output is for eg.

ID KEGSTG KEYSTG without regexp

1151 . . . teeee NB 1.10 1.10 1.10 teeee NB 911


expected is

ID KEGSTG KEYSTG without regexp

1151 teeee NB 911 1.10 1.10 1.10 teeee NB 911

can you please advise
thanks.

regexp_replace [message #679375 is a reply to message #679374] Wed, 26 February 2020 14:25 Go to previous messageGo to next message
akarra
Messages: 24
Registered: August 2011
Location: Housston
Junior Member
Hi I have a requirement
like below


create 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);


commit;


select id,REGEXP_REPLACE(keystg,':digit:'),keystg from test

when i see the output it is removing all number including numbers in string.

coming output is for eg.

ID KEGSTG KEYSTG without regexp

1151 . . . teeee NB 1.10 1.10 1.10 teeee NB 911


expected is

ID KEGSTG KEYSTG without regexp

1151 teeee NB 911 1.10 1.10 1.10 teeee NB 911

can you please advise
thanks.

Re: regexp_replace [message #679376 is a reply to message #679374] Wed, 26 February 2020 14:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Given that you didn't formatted, as usual, it is clear as mud what you want.

Once more:
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

Explain with words what you want and give the result you want for the data you give.

Re: regexp_replace [message #679377 is a reply to message #679376] Wed, 26 February 2020 14:34 Go to previous messageGo to next message
akarra
Messages: 24
Registered: August 2011
Location: Housston
Junior Member
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
Re: regexp_replace [message #679378 is a reply to message #679377] Wed, 26 February 2020 14:35 Go to previous messageGo to next message
akarra
Messages: 24
Registered: August 2011
Location: Housston
Junior Member
CREATE 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);
Re: regexp_replace [message #679379 is a reply to message #679378] Wed, 26 February 2020 14:42 Go to previous messageGo to next message
akarra
Messages: 24
Registered: August 2011
Location: Housston
Junior Member
SELECT
id,
regexp_replace(keystg, ':digit:'),
FROM
test
WHERE
id = 1151

expected out put is to remove all digits and fullstops before string. same is for all records
it should only remove digits before string not within the string.
('1.10 1.10 1.10 teeee NB 911',1151);




ID Keystg

====

1521 teeee NB 911
1152 ter1234 inr testere


thanks

Re: regexp_replace [message #679380 is a reply to message #679379] Wed, 26 February 2020 14:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Is this what you want?
SQL> /
        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

11 rows selected.
Re: regexp_replace [message #679381 is a reply to message #679380] Wed, 26 February 2020 14:51 Go to previous messageGo to next message
akarra
Messages: 24
Registered: August 2011
Location: Housston
Junior Member
yes please
Re: regexp_replace [message #679382 is a reply to message #679381] Wed, 26 February 2020 15:04 Go to previous message
akarra
Messages: 24
Registered: August 2011
Location: Housston
Junior Member
can i know the query please.
thanks.
Previous Topic: Get (and lock) first unlocked row?!
Next Topic: How to Fetch merged rows back !
Goto Forum:
  


Current Time: Thu Mar 28 04:09:11 CDT 2024