Oracle SQL. how to trim a leading substring

Question

I wish to remove a leading substring from a string. I tried to use LTRIM function, but it returned a bad result. In detail

SELECT LTRIM(' AND DAT_MOD = ...', ' AND ') FROM DUAL;

returned

T_MOD = ...

instead of the expected

DAT_MOD = ...

I suppose that I should to convert the ' AND ' string to be trimmed as an object, rather than a set of characters. How can I get it?

What exactly do you want to do? You showed one single example, but what do you want to remove or replace for other data in other rows? — Jonas Metzler
– Jonas Metzler, Commented Mar 27 at 16:19
The reason why your current idea does not work is that LTRIM removes each character in the string you provide as argument. So in your case, it removes every A, N, D and all spaces until the first character appears which is none of them. — Jonas Metzler
– Jonas Metzler, Commented Mar 27 at 16:30

Littlefoot · Accepted Answer · 2025-03-27 20:21:32Z

1

Alternatively, if regular expressions is an option, you could remove ' AND ' from the beginning ^ of the string, e.g.


SQL> select regexp_replace(' AND DAT_MOD = 2 AND ...', '^ AND ') res from dual;

RES
-------------------
DAT_MOD = 2 AND ...

SQL>

answered Mar 27 at 20:21

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

NickW · Accepted Answer · 2025-03-27 16:08:23Z

0

SELECT SUBSTR(' AND DAT_MOD = ...',length(' AND ')) "Substring"
     FROM DUAL;

answered Mar 27 at 16:08

NickW

10k2 gold badges10 silver badges26 bronze badges

You need to increase the length by 1, otherwise you keep the space between AND and DAT_MOD