Home » SQL & PL/SQL » SQL & PL/SQL » update column with value after Rs. (ORACLE 11 G , WIN SERVER 2008)
| update column with value after Rs. [message #690364] |
Tue, 17 June 2025 06:28  |
 |
hissam78
Messages: 199
Registered: August 2011
Location: PAKISTAN
|
Senior Member |
|
|
Dear Experts,
we have following columns, Description column have Rs. character after there is a value like 10, 20, 70 we need help to update the number given after Rs. in column "Width" in given table,
thanks
script is as follows.
CREATE TABLE "TEST"
( "CODE" NUMBER,
"DESCRIPTION" VARCHAR2(200 CHAR),
"WIDTH" NUMBER
) ;
BEGIN
INSERT INTO TEST
(CODE, DESCRIPTION)
VALUES (1002, 'B BUNDLE RS. 20 STMT');
INSERT INTO TEST
(CODE, DESCRIPTION)
VALUES (1001, 'A BUNDLE RS. 10 TST');
INSERT INTO TEST
(CODE, DESCRIPTION)
VALUES (1003, 'C BUNDLE RS - 45');
INSERT INTO TEST
(CODE, DESCRIPTION)
VALUES (1004, 'D-400 BUNDLE RS. - 70');
END;
[Updated on: Tue, 17 June 2025 06:33] Report message to a moderator |
|
|
|
| Re: update column with value after Rs. [message #690365 is a reply to message #690364] |
Tue, 17 June 2025 07:26  |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I cannot remember if 11g had regular expressions or not, so I tried to keep it simple.
The following checks for " RS" in upper, lower, or mixed case, then trims any periods, spaces, or hyphens from there, then trims any spaces and the letters M, S and T from the end. Depending on your data, you may need to add additional characters to the trimming.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM test
2 /
CODE DESCRIPTION WIDTH
---------- ------------------------------ ----------
1002 B BUNDLE RS. 20 STMT
1001 A BUNDLE RS. 10 TST
1003 C BUNDLE RS - 45
1004 D-400 BUNDLE RS. - 70
4 rows selected.
SCOTT@orcl_12.1.0.2.0> UPDATE test
2 SET width =
3 RTRIM
4 (LTRIM
5 (SUBSTR
6 (description,
7 INSTR (UPPER(description), ' RS') + 3),
8 '. -'), -- characters to be removed after RS before number
9 ' MST') -- characters to be removed after number
10 WHERE INSTR (UPPER(description), ' RS') > 0
11 /
4 rows updated.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM test
2 /
CODE DESCRIPTION WIDTH
---------- ------------------------------ ----------
1002 B BUNDLE RS. 20 STMT 20
1001 A BUNDLE RS. 10 TST 10
1003 C BUNDLE RS - 45 45
1004 D-400 BUNDLE RS. - 70 70
4 rows selected.
|
|
|
|
| Re: update column with value after Rs. [message #690366 is a reply to message #690365] |
Tue, 17 June 2025 12:22 |
 |
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Yes, 11g has regexp:
SQL> col description format a30
SQL> select description,
2 to_number(regexp_substr(description, '^.* RS\.? *-? *([0-9]+)', 1, 1, null, 1)) width
3 from test
4 /
DESCRIPTION WIDTH
------------------------------ ----------
B BUNDLE RS. 20 STMT 20
A BUNDLE RS. 10 TST 10
C BUNDLE RS - 45 45
D-400 BUNDLE RS. - 70 70
SQL> @v
Oracle version: 11.2.0.4.181016 EE - JVM v1.6.0_43 - timezone files v11
SQL> update test
2 set width = to_number(regexp_substr(description, '^.* RS\.? *-? *([0-9]+)', 1, 1, null, 1))
3 /
4 rows updated.
SQL> select * from test;
CODE DESCRIPTION WIDTH
---------- ------------------------------ ----------
1002 B BUNDLE RS. 20 STMT 20
1001 A BUNDLE RS. 10 TST 10
1003 C BUNDLE RS - 45 45
1004 D-400 BUNDLE RS. - 70 70
"^.* RS" = anything from the beginning ("^") to " RS"
\.? = maybe ("?") followed by a point
" *" = followed by 0 or more spaces
"-?" = maybe ("?") followed by a "-"
" *" = followed by 0 or more spaces
"([0-9]+)" = then followed by 1 or more digits, the "(" and ")" memorize these digits in a variable named "1" (the first occurrence of "()") we retrieve as the last parameter of REGEXP_SUBSTR.
[Updated on: Tue, 17 June 2025 16:05] Report message to a moderator |
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Jul 19 16:37:59 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
