Home » SQL & PL/SQL » SQL & PL/SQL » How to campare string in PL/SQL (Oracle 10g)
How to campare string in PL/SQL [message #316447] Fri, 25 April 2008 02:53 Go to next message
AnamikaChaudhary
Messages: 33
Registered: December 2007
Location: Mumbai
Member
Hi,

I have to compare a string entered in a fields on forms with the column in a table.

Eg:
i have FOODSTUFFS in my table ...but while entering this value on FORMS some may spell wrong.
then how can i compare my string with the value in table.

Please help me on this

Thanks
Anamika



Re: How to campare string in PL/SQL [message #316455 is a reply to message #316447] Fri, 25 April 2008 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
then how can i compare my string with the value in table

Which one if the value entered is not the correct one?

Regards
Michel
Re: How to compare string in PL/SQL [message #316459 is a reply to message #316455] Fri, 25 April 2008 03:27 Go to previous messageGo to next message
AnamikaChaudhary
Messages: 33
Registered: December 2007
Location: Mumbai
Member
Hi,

spelled wrong FOODSTUFFS with Foodstuffs in database.

Example:

FOODSSTUFS(spelled wrong) with FOODSTUFFS (value in table).

Thanks
Anamika
Re: How to compare string in PL/SQL [message #316461 is a reply to message #316459] Fri, 25 April 2008 03:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If this is just a matter of case, have a look at LOWER and UPPER functions.
In this case, it is better to always store with the same case in the database to take profit of (normal) index.

Regards
Michel
Re: How to campare string in PL/SQL [message #316463 is a reply to message #316447] Fri, 25 April 2008 04:02 Go to previous messageGo to next message
AnamikaChaudhary
Messages: 33
Registered: December 2007
Location: Mumbai
Member
Hi,

I think i am not explaining properly, what i mean to say.

I need to match each word written in a field against a
table. If there is a match, then based on a flag in the table, certain actions will happen, either a pop-up warning written simply not be able proceed with the booking.

while finding a match ,also i have to check the spelling.
In this case match is there but spelling is wrong.

In example in mentioned that i have FOODSTUFFS in my table
but some may spell wrong then it will display match not found,but the match is there only spelling is wrong.


I think now i must be clear my point.

Thanks
Anamika
Re: How to campare string in PL/SQL [message #316464 is a reply to message #316463] Fri, 25 April 2008 04:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I think now i must be clear my point.

Not for me. Why lower/upper don't fit your requirements?
Post examples.

Regards
Michel
Re: How to compare string in PL/SQL [message #316466 is a reply to message #316459] Fri, 25 April 2008 04:15 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

spelled wrong FOODSTUFFS with Foodstuffs in database.


FOODSSTUFS(spelled wrong) with FOODSTUFFS (value in table).


I believe you meant either of these two could be true
a) Either they could be typed in mixed cases but with the correct spelling
b) They could be either upper or lower case but they could be typed incorrectly.

Is this what you are saying ?

For option a) you can use what michael has suggested.
For option b) It is bit difficult. To start with try this

SQL> select soundex('Foodstuff') from dual;

SOUN
----
F323

SQL> select soundex('Foodsstuff') from dual;

SOUN
----
F323

SQL> select soundex('FOODSStuff') from dual;

SOUN
----
F323

Give that a try and do some research and see whether it matches your requirement.

Regards

Raj

[Updated on: Fri, 25 April 2008 04:19]

Report message to a moderator

Re: How to compare string in PL/SQL [message #316470 is a reply to message #316466] Fri, 25 April 2008 04:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A precision: soundex only works for english.

Regards
Michel
Re: How to compare string in PL/SQL [message #316473 is a reply to message #316470] Fri, 25 April 2008 04:50 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
As always you are valuable. I learnt something new today.

Regards

Raj
Re: How to compare string in PL/SQL [message #316489 is a reply to message #316466] Fri, 25 April 2008 06:21 Go to previous messageGo to next message
AnamikaChaudhary
Messages: 33
Registered: December 2007
Location: Mumbai
Member
Hi,

My case is second one

Quote:
They could be either upper or lower case but they could be typed incorrectly.



I have to make a procedure or function for this(spell check)
beco'z i have a large no data into the table.

Thanks
Anamika
Re: How to compare string in PL/SQL [message #316511 is a reply to message #316489] Fri, 25 April 2008 07:53 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
You might want to give a look at Oracle Text.
Re: How to compare string in PL/SQL [message #316546 is a reply to message #316511] Fri, 25 April 2008 11:23 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8632
Registered: November 2002
Location: California, USA
Senior Member
A long time ago I wrote a function that finds the closest_match between two strings or a string and a set of strings. So, if you have a list of values in a table:

SCOTT@orcl_11g> SELECT ename FROM emp
2 /

ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

14 rows selected.

you can find the closest match for any string that was entered. So, searching for FJORD will find FORD form the list above:

SCOTT@orcl_11g> SELECT closest_match (match_input (ename, 'FJORD')) FROM emp
2 /

CLOSEST_MATCH(MATCH_INPUT(ENAME,'FJORD'))
--------------------------------------------------------------------------------
FORD

SCOTT@orcl_11g>

So, if your application does not find an exact match in the lookup table, it can use this to provide a value for a possible misspelling and prompt the user to accept it or decline and re-enter. There are also various string comparison utilities available for string comparison in 10g such as utl_match.edit_distance and utl_match.edit_distance_similarity and utl_match.jaro_winkler and utl_match.jaro_winkler_similarity. These would allow you to obtain different scores which you could then order, rather than returning just the one closest value. Edit distance relies purely on string comparison, while jaro_winkler is intended specifically for names. There are others such as double metaphone which is similar to soundex.

Here is the code for creating my closest_match function:

CREATE OR REPLACE FUNCTION ld -- Levenshtein distance
  (p_source_string   IN VARCHAR2,
   p_target_string   IN VARCHAR2)
  RETURN                NUMBER
  DETERMINISTIC
AS
  v_length_of_source    NUMBER := NVL (LENGTH (p_source_string), 0);
  v_length_of_target    NUMBER := NVL (LENGTH (p_target_string), 0);
  TYPE mytabtype IS     TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  column_to_left        mytabtype;
  current_column        mytabtype;
  v_cost                NUMBER := 0;
BEGIN
  IF p_source_string = p_target_string THEN
    RETURN 0;
  ELSIF v_length_of_source = 0 THEN
    RETURN v_length_of_target;
  ELSIF v_length_of_target = 0 THEN
    RETURN v_length_of_source;
  ELSE
    FOR j IN 0 .. v_length_of_target LOOP
      column_to_left(j) := j;
    END LOOP;
    FOR i IN 1.. v_length_of_source LOOP
      current_column(0) := i;
      FOR j IN 1 .. v_length_of_target LOOP
        IF SUBSTR (p_source_string, i, 1) =
           SUBSTR (p_target_string, j, 1)
        THEN v_cost := 0;
        ELSE v_cost := 1;
        END IF;
        current_column(j) := LEAST (current_column(j-1) + 1,
                                    column_to_left(j) + 1,
                                    column_to_left(j-1) + v_cost);
      END LOOP;
      FOR j IN 0 .. v_length_of_target  LOOP
        column_to_left(j) := current_column(j);
      END LOOP;
    END LOOP;
  END IF;
  RETURN current_column(v_length_of_target);
END ld;
/
CREATE OR REPLACE TYPE match_input AS OBJECT
  (str_value             VARCHAR2(4000),
   str_tocompare         VARCHAR2(4000));
/
CREATE OR REPLACE TYPE match_t AS OBJECT
  (str_agg               VARCHAR2(4000),
   lev_dist              NUMBER,
   min_val               NUMBER,
   STATIC FUNCTION ODCIAggregateInitialize 
     (sctx        IN OUT match_t) 
     RETURN              NUMBER,
   MEMBER FUNCTION ODCIAggregateIterate 
     (self        IN OUT match_t, 
      value       IN     match_input) 
     RETURN              NUMBER,
   MEMBER FUNCTION ODCIAggregateTerminate 
     (self        IN     match_t, 
      returnValue OUT    VARCHAR2, 
      flags       IN     NUMBER) 
     RETURN              NUMBER,
   MEMBER FUNCTION ODCIAggregateMerge 
     (self        IN OUT match_t, 
      ctx2        IN     match_t) 
     RETURN              NUMBER);
/
CREATE OR REPLACE TYPE BODY match_t AS 
  STATIC FUNCTION ODCIAggregateInitialize
    (sctx         IN OUT match_t)
    RETURN               NUMBER 
  IS
  BEGIN
    sctx := match_t (NULL, NULL, NULL);
    RETURN ODCIConst.Success;
  END ODCIAggregateInitialize;

  MEMBER FUNCTION ODCIAggregateIterate
    (self         IN OUT match_t, 
     value        IN     match_input)
    RETURN               NUMBER 
  IS
  BEGIN
    IF self.str_agg IS NULL THEN
      self.str_agg := value.str_value;
      self.lev_dist := ld (value.str_value, value.str_tocompare);
      self.min_val := ld (value.str_value, value.str_tocompare);
    END IF;

    IF ld (value.str_value, value.str_tocompare) < self.min_val THEN
      self.str_agg := value.str_value;
      self.min_val := ld (value.STR_VALUE, value.str_tocompare);
    END IF;
    RETURN ODCIConst.Success;
  END ODCIAggregateIterate;

  MEMBER FUNCTION ODCIAggregateTerminate
    (self         IN     match_t, 
     returnValue  OUT    VARCHAR2, 
     flags        IN     NUMBER) 
    RETURN               NUMBER 
  IS
  BEGIN
    returnValue := str_agg;
    RETURN ODCIConst.Success;
  END ODCIAggregateTerminate;

  MEMBER FUNCTION ODCIAggregateMerge
    (self         IN OUT match_t, 
     ctx2         IN     match_t) 
    RETURN               NUMBER 
  IS
    c_self               VARCHAR2(1);
    c_ctx2               VARCHAR2(1);
    c_ini                VARCHAR2(1);
    i                    INTEGER;
  BEGIN
    IF ctx2.lev_dist < self.lev_dist THEN
      self.str_agg := ctx2.str_agg;
    END IF;
    RETURN ODCIConst.Success;
  END ODCIAggregateMerge;
END;
/
CREATE OR REPLACE FUNCTION closest_match
  (input match_input) 
  RETURN VARCHAR2
  PARALLEL_ENABLE AGGREGATE USING match_t;
/



Re: How to compare string in PL/SQL [message #316566 is a reply to message #316511] Fri, 25 April 2008 14:20 Go to previous message
Barbara Boehmer
Messages: 8632
Registered: November 2002
Location: California, USA
Senior Member
When I originally wrote the closest_match function, the utl_match package wasn't available yet, so I used the ld function that I had written previously. Since utl_match has been available since 10g, I rewrote the function as closest_match_plus with a third parameter to allow you to choose between edit_distance comparison method which only considers the number of different character additions, removals, and replacements, and jaro_winkler comparison method which utilizes an algorithm that considers the sound. So, you may get different results from each:

SCOTT@orcl_11g> CREATE TABLE test_tab (test_col VARCHAR2 (30))
2 /

Table created.

SCOTT@orcl_11g> INSERT ALL
2 INTO test_tab VALUES ('SMYTHE')
3 INTO test_tab VALUES ('SMATH')
4 INTO test_tab VALUES ('JONES')
5 SELECT * FROM DUAL
6 /

3 rows created.

SCOTT@orcl_11g> SELECT closest_match_plus (match_input_plus (test_col, 'SMITH', 'ed')) FROM test_tab
2 /

CLOSEST_MATCH_PLUS(MATCH_INPUT_PLUS(TEST_COL,'SMITH','ED'))
--------------------------------------------------------------------------------
SMATH

SCOTT@orcl_11g> SELECT closest_match_plus (match_input_plus (test_col, 'SMITH', 'jw')) FROM test_tab
2 /

CLOSEST_MATCH_PLUS(MATCH_INPUT_PLUS(TEST_COL,'SMITH','JW'))
--------------------------------------------------------------------------------
SMYTHE

SCOTT@orcl_11g>

Here is the revised code:

CREATE OR REPLACE TYPE match_input_plus AS OBJECT
  (str_value             VARCHAR2(4000),
   str_tocompare         VARCHAR2(4000),
   comp_method           VARCHAR2(4000)
   -- values for comp_method parameter:
   -- enter ED for utl_match.edit_distance
   -- enter JW for utl_match.jaro_winkler
  );
/
SHOW ERRORS
CREATE OR REPLACE TYPE match_t_plus AS OBJECT
  (str_agg               VARCHAR2(4000),
   lev_dist              NUMBER,
   min_val               NUMBER,
   STATIC FUNCTION ODCIAggregateInitialize 
     (sctx        IN OUT match_t_plus) 
     RETURN              NUMBER,
   MEMBER FUNCTION ODCIAggregateIterate 
     (self        IN OUT match_t_plus, 
      value       IN     match_input_plus) 
     RETURN              NUMBER,
   MEMBER FUNCTION ODCIAggregateTerminate 
     (self        IN     match_t_plus, 
      returnValue OUT    VARCHAR2, 
      flags       IN     NUMBER) 
     RETURN              NUMBER,
   MEMBER FUNCTION ODCIAggregateMerge 
     (self        IN OUT match_t_plus, 
      ctx2        IN     match_t_plus) 
     RETURN              NUMBER);
/
SHOW ERRORS
CREATE OR REPLACE TYPE BODY match_t_plus AS 
  STATIC FUNCTION ODCIAggregateInitialize
    (sctx         IN OUT match_t_plus)
    RETURN               NUMBER 
  IS
  BEGIN
    sctx := match_t_plus (NULL, NULL, NULL);
    RETURN ODCIConst.Success;
  END ODCIAggregateInitialize;

  MEMBER FUNCTION ODCIAggregateIterate
    (self         IN OUT match_t_plus, 
     value        IN     match_input_plus)
    RETURN               NUMBER 
  IS
  BEGIN
    IF self.str_agg IS NULL THEN
      self.str_agg := value.str_value;
      IF UPPER (value.comp_method) = 'ED' THEN
        self.lev_dist := utl_match.edit_distance (value.str_value, value.str_tocompare);
        self.min_val := utl_match.edit_distance (value.str_value, value.str_tocompare);
      ELSIF UPPER (value.comp_method) = 'JW' THEN
        self.lev_dist := utl_match.jaro_winkler (value.str_value, value.str_tocompare);
        self.min_val := utl_match.jaro_winkler (value.str_value, value.str_tocompare);
      END IF;
    END IF;

    IF utl_match.edit_distance (value.str_value, value.str_tocompare) < self.min_val THEN
      self.str_agg := value.str_value;
      IF UPPER (value.comp_method) = 'ED' THEN
        self.min_val := utl_match.edit_distance (value.STR_VALUE, value.str_tocompare);
      ELSIF UPPER (value.comp_method) = 'JW' THEN
        self.min_val := utl_match.jaro_winkler (value.STR_VALUE, value.str_tocompare);
      END IF;
    END IF;
    RETURN ODCIConst.Success;
  END ODCIAggregateIterate;

  MEMBER FUNCTION ODCIAggregateTerminate
    (self         IN     match_t_plus, 
     returnValue  OUT    VARCHAR2, 
     flags        IN     NUMBER) 
    RETURN               NUMBER 
  IS
  BEGIN
    returnValue := str_agg;
    RETURN ODCIConst.Success;
  END ODCIAggregateTerminate;

  MEMBER FUNCTION ODCIAggregateMerge
    (self         IN OUT match_t_plus, 
     ctx2         IN     match_t_plus) 
    RETURN               NUMBER 
  IS
    c_self               VARCHAR2(1);
    c_ctx2               VARCHAR2(1);
    c_ini                VARCHAR2(1);
    i                    INTEGER;
  BEGIN
    IF ctx2.lev_dist < self.lev_dist THEN
      self.str_agg := ctx2.str_agg;
    END IF;
    RETURN ODCIConst.Success;
  END ODCIAggregateMerge;
END;
/
SHOW ERRORS
CREATE OR REPLACE FUNCTION closest_match_plus
  (input match_input_plus) 
  RETURN VARCHAR2
  PARALLEL_ENABLE AGGREGATE USING match_t_plus;
/
SHOW ERRORS


Previous Topic: ora-00600 during select query execution...
Next Topic: Accented Vowels
Goto Forum:
  


Current Time: Tue Dec 06 02:33:32 CST 2016

Total time taken to generate the page: 0.09682 seconds