Using soundex in OutSystems

Hi all,

According to wiki page:
«Soundex is a phonetic algorithm for indexing names by sound, as pronounced in English.»

Fortunately SQL Server (from 2000 on) and Oracle (8i, 9i, 10g, 11g) already implement this algorithm as a soundex function, hence there's no compatibility issue between the different database engines.

You can use this to implement sounds-like search queries. To do it in OutSystems you need to use an Advanced Query with something like.

SELECT * FROM {User} WHERE soundex({User}.[Name]} = soundex(@search)

Since the algorithm produces a code the comparison should be made with an equal (=) instead of a like.

Also see attached a study about this algorithm.

Hope this helps you all,
Cheers,
André

Hi André,

This soundex feature is useful, but quite limited:

• it is only adapted to English;
• it assumes the argument is a single word (normally {User}.[Name] has more than one name).

If you are serious about text search, you should consider using full text search mechanisms either from the database or from external tools.

Cheers,
Lúcio

Anybody already used the SQL Server Full Text Search ?

We've tried it, however there are some limitation in Service Studio.
You have to create an Advanced Query joining the OutSystems tables to the SQL Full Text tables.
Service Studio is not capable of validating the query and the eSpace will give warnings ...

This is a sample of such advanced query:
SELECT FT_TBL.DESCRIPTION,FT_TBL.NUMBER, KEY_TBL.RANK
FROM dbo.OSUSR_J94_ARTICLE4 AS FT_TBL

INNER JOIN CONTAINSTABLE(dbo.OSUSR_J94_ARTICLE4,*, 'ISABOUT (Grohe* weight (.5), Costa* weight (.6), kraan* weight (.2) )',500 ) AS KEY_TBL

ON FT_TBL.IDENTIFIER = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC;


Anybody out there having more/better experiences ?

Yes i would like to know too

Luis,

What do you want to do now? Soundex or the full text search?

Kind regards,
Evert

If you just want soundex and don't want to use an advanced query, you can find a soundex .NET or Java library, wrap it in Integration Studio, and then do a post-query filtering (ForEach through the query results, and if the soundex check passes, copy the result to another list).

J.Ja