How to get the value between 2 characters in oracle sql

get string between two special characters oracle
how to select a substring in oracle sql after a specific character
oracle sql substring after character
how to get last 4 characters in a string in oracle
how to find multiple occurrences of a string in oracle
oracle regexp_substr between two commas
oracle sql substring after last character
regexp_substr level

Hi everyone i have like this value in my column:

<?xml version="1.0" encoding="UTF-8"?><?dsk-revision 1.0?><Descriptor><pde><a><dov>784512</dov></a></pde></Descriptor>

I would retrieve just :

<dov>784512</dov>

Do you have any ideas?

Thank you !

You can use regexp_replace():

select x.*,
       regexp_replace(str, '<pde><a>(.*)</a></pde>', '\1')
from (select '<pde><a><dov>784512</dov></a></pde>' as str from dual) x;

Or regexp_substr():

select x.*,
       regexp_substr(str, '<dov>.*</dov>')
from (select '<pde><a><dov>784512</dov></a></pde>' as str from dual) x;

I am unclear whether you want the string because the "dov"s or between the "pde/a"s.

How to get text from between characters in oracle sql, Suppose your data resides in a tabled named test_n with only column val with above values as mentioned in your question, the query is: select� Description: While working with sql server database I got the requirement to get substring from within two specific characters or symbols. To get this done we just need to find the first and last index of specified character and then using substring function we can extract the desired part as demonstrated in example below.

With SUBSTR + INSTR:

SQL> with test (col) as
  2  (select '<pde><a><dov>784512</dov></a></pde>' from dual)
  3  select substr(col, instr(col, '<dov>', 1, 1),
  4                     instr(col, '</dov>', 1, 1) + 6 - instr(col, '<dov>')) result
  5  from test;

RESULT
-----------------
<dov>784512</dov>

SQL>

REGEXP_SUBSTR, Go to main content Database SQL Reference source_char is a character expression that serves as the search value. position is a positive integer indicating the character of source_char where Oracle should begin the search. occurrences of this substring between http:// and either a slash ( / ) or the end of the string. Oracle SQL to get values between 2 special characters [closed] I'm looking for a Oracle SQL query to get value 'LATCH' From 'RTG/LATCH.PETROL_ABC' Thanks.

Use REGEXP_REPLACE with specified mask:

select x.*,
       regexp_replace(str, '.*<a>(.*)</a>.*', '\1'), -- anything between <a> </a>
       regexp_replace(str, '.*(<dov>.*</dov>).*', '\1'), -- anything between <dov> </dov> tag (tag included)
       regexp_replace(str, '.*<pde><a>(.*)</a></pde>.*', '\1'), -- anything between <pde><a> </a></pde>
       regexp_replace(str, '.*<pde><a>(<dov>.*</dov>)</a></pde>.*', '\1') -- anything between <pde><a> </a></pde> that is inside <dov> </dov> tag (tag included)
from (select '<pde><a><dov>784512</dov></a></pde>' as str from dual) x;

Ask TOM "How extract text between 2 strings and get multipl", How extract text between 2 strings and get multiple occurances Category: SQL *Plus - Version: 12.1 EMP1 - 02/15/16 09:32 PST ***Sallary Approved - Emp1EMP2***** EMP2 - 02/19/16 07:59 ***What is the value of raise. Hi. Can anyone help me with an easy way to extract a substring that is between 2 characters? Example string: 123n_abcn_123n . The substrings before and after the underscores can be any length.

SUBSTR functions, Home / Database / Oracle Database Online Documentation 11g Release 1 (11.1) The functions vary in how they calculate the length of the substring to return. The position at which the first character of the returned string begins. a value for this argument, then the function returns all characters to the end of string. Hi, I have a column in a table called "loginname" with different values. sample values in that column:-1) w|001c\test1. 2) w|msftedu\test2. I am trying to extract everything between PipeSymbol(|) and BackSlash(\), excluding those two symbols.

SUBSTR, Home / Database / Oracle Database Online Documentation, 10g Release 2 (10.2 ) / Database SQL Reference The SUBSTR functions return a portion of char , beginning at character then Oracle Database counts from the beginning of char to find the first character. The return value is the same datatype as char . BETWEEN Conditions . A BETWEEN condition determines whether the value of one expression is in an interval defined by two other expressions. between_condition::= Description of the illustration between_condition.gif. All three expressions must be numeric, character, or datetime expressions. In SQL, it is possible that expr1 will be evaluated

Oracle SQL: Extracting text between two characters, how to select a substring in oracle sql after a specific character Oracle SQL to get values between 2 special characters [closed] closed as unclear what you're� SQL Server: -- Get last 4 characters in string SELECT RIGHT ('New York', 4); # York. You can also use RIGHT function if the absolute values of the negative start position and length are equal: Oracle: -- Get last 4 characters in string SELECT SUBSTR ('New York',-4, 4) FROM dual; # York. SQL Server:

Comments
  • what have you tried to retrieve so far?
  • Is the data type of your column XMLTYPE?
  • @Boneist column type was string and value was a XML value (<?xml version="1.0" encoding="UTF-8"?> <?dsk-revision 1.0?> <Descriptor>...) but it was saved as string value.