searching within contents of a cell in ORACLE db using sql only

search text in oracle database
oracle contains vs like
oracle contains multiple words
oracle sql developer search for text
oracle sql contains
oracle text contains wildcard search
oracle sql like
oracle check if string contains letters

I am writing a standard SQL query that returns, among others, values of column called DETAILS:

select t.id, t.details
from table_1 t

Each cell of the Details column has a long text arranged in a specific order.

Sample text goes like this:

description (75535183778272) whereto address1-address amount 23301600 livesat address3

In other words Details cell always contains description, whereto, amount, livesat categories. And each of this categories is followed by a value of different length.

The question is how to construct a query which would give me only value of Amount category in a cell?

I hope my question is clear. Thanks in advance.

Try smth like this:

select  t.id, 
        t.details, 
        regexp_substr(t.details, 'amount ([0-9]+)',1,1,null,1) amount 
  from  table_1 t;

You may also want to convert the amount into a numeric type immediately.

PS: My example works for positive integer amount only. You may want to modify it to accept decimal and negative values.

Querying with Oracle Text, To enter an Oracle Text query, use the SQL SELECT statement. With CONTAINS , you can also use the ABOUT operator to search on document themes. If multiple words are contained in a query expression, separated only by When you include a stopword within your query phrase, the stopword matches any word. Figure 25-1 shows how Oracle XML DB RESOURCE_VIEW and PATH_VIEW provide a mechanism for using SQL to access data stored in Oracle XML DB Repository. Data stored in the repository using protocols such as FTP and WebDAV, or using application program interfaces (APIs), can be accessed in SQL using RESOURCE_VIEW values and PATH_VIEW values.

How about this:

select t.id,
       t.description,
       regexp_substr(t.description, '[^[:space:]]+', 1, 6) amount
from table_1 t

Assuming the amount is always the 6th element...

Tutorial: Querying and Filtering Rows: Databases , You access rows in a database table with the select statement. This returns data Usually you only want those matching some search criteria. Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information. Learn more searching within contents of a cell in ORACLE db using sql only

WITH Table_1 AS (SELECT 'description (75535183778272) whereto address1-address amount 23301600 livesat address3' AS Details FROM dual)
SELECT
     Details
    ,INSTR(Details, 'amount') + 06  AS amount_Pos
    ,INSTR(Details, 'livesat') AS livesat_Pos
    ,SUBSTR
        (
         Details
        ,INSTR(Details, 'amount') + 06
        ,(INSTR(Details, 'livesat') - INSTR(Details, 'amount')) - 07
        )                    AS Amount_Str
FROM Table_1
;

CONTAINS SQL Example, Database Online Documentation Library, 12c Release 1 (12.1.0.2). Text Application Developer's Guide. Contents. Previous · Next. Page 122 of 321. Search. You can use the ABOUT operator with the CONTAINS and CATSEARCH SQL operators. In the case of CATSEARCH, you must use query templating with the CONTEXT grammar to query on the indexed themes. See ABOUT Query with CATSEARCH in the Examples section. about ( phrase) In all languages, increases the number of relevant documents returned for the same

Selecting Table Data, For instructions, see "Connecting to Oracle Database as User HR from SQL Developer". A query nested within another SQL statement is called a subquery. field does not have the value hr_conn , select that value from the drop-down list​. The query in Example 4-3 selects data only for employees in department 90. With Oracle Text you can use PL/SQL packages for document presentation and thesaurus maintenance. You can query XML data stored in the database directly, without using Oracle Text. However, Oracle Text is useful for boosting query performance.

Querying and Manipulating Data, For example, every object in a database belongs to only one schema, and has a unique of a query, and a query nested within another SQL statement is called a subquery. The result will contain eight rows, with four rows matching the first value in the list, and Looking at an intersection of two tables is a JOIN operation. 4.1.1.2 CONTAINS PL/SQL Example. In a PL/SQL application, you can use a cursor to fetch the results of the query. The following example enters a CONTAINS query against the NEWS table to find all articles that contain the word oracle.

Pattern-matching Conditions, Database SQL Language Reference a portion of one character value to another by searching the first value for the pattern specified by the second. Within y , the character % matches any string of zero or more characters except null. character, then the condition can be true only if both operands have the same length. Oracle Database 18c PL/SQL. PL/SQL is a procedural language designed specifically to embrace SQL statements within its syntax. PL/SQL program units are compiled by the Oracle Database server and stored inside the database. And at run-time, both PL/SQL and SQL run within the same server process, bringing optimal efficiency.

Comments
  • Does 'livesat' always follow 'amount'? And is there even the remotest chance either of those strings will ever appear elsewhere in the long text?
  • Yes, 'livesat' always comes after 'amount'. No, they will not appear elswehere within the given cell.
  • This is not how you are supposed to use a database. Why don't you have separate columns for the values?
  • Are you using Oracle or not? The answer will depend on the dbms actually used...
  • yes, I am using Oracle 11g