Skip to Content

A minor correction in SAP HANA Developer’s Guide for the SQL Function Fuzzy Search  SAP HANA Appliance Software SPS 04

on the page 20 following  section

DROP TABLE test_spell_check_factor;

CREATE COLUMN TABLE test_spell_check_factor

(

id INTEGER PRIMARY KEY,

s NVARCHAR(255),

t SHORTTEXT(200) FUZZY SEARCH INDEX ON

);

INSERT INTO test_spell_check_factor VALUES (‘1′,’Muller’,’Muller’);

INSERT INTO test_spell_check_factor VALUES (‘2′,’Mueller’,’Mueller’);

INSERT INTO test_spell_check_factor VALUES (‘3′,’Müller’,’Müller’);

INSERT INTO test_spell_check_factor VALUES (‘4′,’Möller’,’Möller’);

has to be changed to

DROP TABLE test_spell_check_factor;

CREATE COLUMN TABLE test_spell_check_factor

(

id INTEGER PRIMARY KEY,

s TEXT,

t SHORTTEXT(200) FUZZY SEARCH INDEX ON

);

INSERT INTO test_spell_check_factor VALUES (‘1′,’Muller’,’Muller’);

INSERT INTO test_spell_check_factor VALUES (‘2′,’Mueller’,’Mueller’);

INSERT INTO test_spell_check_factor VALUES (‘3′,’Müller’,’Müller’);

INSERT INTO test_spell_check_factor VALUES (‘4′,’Möller’,’Möller’);

The datatype of column s has to be changed from NVARCHAR(255) to TEXT because  the following query returns “SAP DBTech JDBC: [2048]: column store error: search table error:  [2018] Option ‘textSearch’ only allowed for TEXT columns (column ‘S’)” error

SELECT SCORE() AS score, id, s

FROM text_spell_check_factor

WHERe CONTAINS(s, ‘Müller’, FUZZY(0.5, ‘spellCheckFactor=0.9,textSearch=compare’))

ORDER BY score DESC;

Hope this helps.

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Joerg Meyer

    Hi Jagadeshwar,

    Actually, there is an error in the documentation.

    The first example shows the spellCheckFactor on string columns. So the error is not in the create table statement, it is in the search options parameter.

    The correct select statement is

    SELECT SCORE() AS score, id, s

    FROM text_spell_check_factor

    WHERE CONTAINS(s, ‘Müller’, FUZZY(0.5, ‘spellCheckFactor=0.9’))

    ORDER BY score DESC;

    Regards

    Jörg

    (0) 

Leave a Reply