logo
down
shadow

Oracle Contains search with Apostrope


Oracle Contains search with Apostrope

By : user2185328
Date : November 21 2020, 04:01 AM
wish helps you Assume those sample data - see below the CTAS statement to create them.
code :
FIRSTNAME LASTNAME
--------- --------
D'Arch    O'Neil   
DArch     ONeil    
D Arch    O Neil   
D.Arch    O.Neil 
BEGIN
  ctxsys.ctx_ddl.create_preference('lex', 'BASIC_LEXER');
  ctxsys.ctx_ddl.set_attribute('lex', 'printjoins', '''');  
END;
/
---
BEGIN
  ctxsys.ctx_ddl.create_preference('pref', 'MULTI_COLUMN_DATASTORE');
  ctx_ddl.set_attribute('pref', 'columns', 'firstname, lastname');
END;
/

create index idx_test on test(firstname)
indextype is CTXSYS.CONTEXT
parameters ('datastore  pref LEXER  lex')
;
select TOKEN_TEXT from DR$IDX_TEST$I;

TOKEN_TEXT                                                     
----------------------------------------------------------------
ARCH                                                             
D'ARCH                                                           
DARCH                                                            
FIRSTNAME                                                        
LASTNAME                                                         
NEIL                                                             
O                                                                
O'NEIL                                                           
ONEIL                                                            
select * 
from test 
where CONTAINS (firstname, '%D''Ar%') >0;

FIRSTNAME LASTNAME
--------- --------
D'Arch    O'Neil 
select * 
from test 
where CONTAINS (firstname, '%O''Ne%') >0;

FIRSTNAME LASTNAME
--------- --------
D'Arch    O'Neil
create table test as
select 'D''Arch' firstname, 'O''Neil' Lastname from dual union all
select 'DArch' firstname, 'ONeil' Lastname from dual union all
select 'D Arch' firstname, 'O Neil' Lastname from dual union all
select 'D.Arch' firstname, 'O.Neil' Lastname from dual;


Share : facebook icon twitter icon
Asp.net, oracle sql, Basically search multiple strings in two columns name, prename from textbox with oracle database

Asp.net, oracle sql, Basically search multiple strings in two columns name, prename from textbox with oracle database


By : Mr.H
Date : March 29 2020, 07:55 AM
may help you . Try changing your SQL query as follows.(I assume your other column is "prename")
code :
strSQl = "select * from angajati where nume ='" + this.ToTitleCase(Nume.Text) + "' or prename="+ this.ToTitleCase(Nume.Text) + "';
Functions in Oracle. Need to search and find the passed paramater using Oracle Function

Functions in Oracle. Need to search and find the passed paramater using Oracle Function


By : user2010970
Date : March 29 2020, 07:55 AM
This might help you I have a requirement like , I need to search and find a value of a table column using Oracle Parameterized function.
code :
create or replace function does_name_exist(p_name in varchar2)
    return varchar2 is
    v_exists varchar2(1);
begin
    -- validate parameter
    select 'Y' into v_exists from my_table where name = p_name;

    -- if it exists
    return p_name;

    -- if it doesn't exist
    exception when NO_DATA_FOUND then
        return 'passed name value doesn''t exist';
end;
/
How to escape apostrope value

How to escape apostrope value


By : Rob T
Date : March 29 2020, 07:55 AM
hope this fix your issue My code is like this , Have you tryed with traditiona escape like:
unable to save image in widget apostrope

unable to save image in widget apostrope


By : Dominic Jagschitz
Date : March 29 2020, 07:55 AM
hope this fix your issue I don't have much knowledge about apostrophe but I tried to create a custom widget. I want three fields in my widget: , You can add an image widget to your widget's schema like this
Search pattern and remove search string characters if not found and then search it again in oracle sql

Search pattern and remove search string characters if not found and then search it again in oracle sql


By : Qousar_Bashir
Date : March 29 2020, 07:55 AM
this will help Search once for any value containing 6, 7, 8 or 9, order by how well the value matches '6789' and take the top result.
(Edit: I've added an instr expression to prioritise occurrences of the exact string '6789' over the JW score, as otherwise '678 blah' would rank higher than 'blah 6789'.)
code :
create table demo (col1) as
( select column_value
  from   table(sys.dbms_debug_vc2coll
         ( '6789','678X','X789','67XX','XX89','XXXX','6XXX','X7XX','11111167891111'
         , '167891','Germany 6, England 7 what a great match that was in 1989')) );

select col1
     , utl_match.jaro_winkler_similarity(col1,'6789') as match_rating
     , nullif(instr(col1,'6789'),0) as match_position
from   demo
where  regexp_like(col1,'[6789]')
order by match_position nulls last, match_rating desc;

COL1                                                     MATCH_RATING MATCH_POSITION
-------------------------------------------------------- ------------ --------------
6789                                                              100              1
167891                                                             88              2
11111167891111                                                     59              7
678X                                                               88 
X789                                                               83 
67XX                                                               73 
XX89                                                               66 
6XXX                                                               55 
X7XX                                                               50 
Germany 6, England 7 what a great match that was in 1989           34 

10 rows selected
select col1
from   demo
where  regexp_like(col1,'[6789]')
order by
       nullif(instr(col1,'6789'),0) nulls last
     , utl_match.jaro_winkler_similarity(col1,'6789') desc
fetch first row only;

COL1
-----
6789
select col1
from   ( select col1
         from   demo
         where  regexp_like(col1,'[6789]')
         order by
                nullif(instr(col1, '6789'),0) nulls last
              , utl_match.jaro_winkler_similarity(col1,'6789') desc )
where rownum = 1;
select col1 into :result
from   demo
where  regexp_like(col1,'['||p_pattern||']')
order by
       nullif(instr(col1, p_pattern),0) nulls last
     , utl_match.jaro_winkler_similarity(col1,p_pattern) desc
fetch first row only;
Related Posts Related Posts :
  • Stored Procedure using Execute Immediate with binding
  • Spring Data JPA + Oracle Trigger increments the ID twice
  • create catsearch index for comma delimited values in oracle
  • Oracle regexp_like failing on FLOAT from view
  • PLSQL ORACLE : Inner join between table variables
  • Oracle does not use invisible index despite of hint
  • Add inline function with IF/ELSE clause in Oracle 12c
  • create trigger for "Not allow any other connection into database other than user having DBA priviledge"
  • LISTAGG inside a LISTAGG
  • Turn on heat_map parameter in Oracle 12c
  • "non-existent directory or file for FILEOPEN operation" when generating signature of an image in oracle 11g?
  • Add element to a varray data type
  • if(select column from table where column = :var_input from table)? wouldn't work :(
  • Update a column of one table with a column of another table 2 without relationship
  • Table is mutating, trigger/function may not see it error oracle db
  • Pre Populate Text Fields in Apex
  • Return success/failure and error message of transaction?
  • Oracle database space
  • Compare two schemas and update the old schema with the new columns of new schema
  • Oracle - Fetch date/time in milliseconds from DATE datatype field
  • Very long SQL connection opening time
  • I want to learn PL/SQL
  • Coalesce in Oracle
  • Does anyone use the PL/SQL Web Toolkit?
  • Oracle xmltype extract function never deallocate/reclaim memory until session down
  • Setting the primary key of a object type table in Oracle
  • How to connect to two databases of Oracle using dblink
  • shadow
    Privacy Policy - Terms - Contact Us © bighow.org