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.
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