Question 66409 in Javascript Question posted in
A very good W3school tutorial can be found here.

Get records with similar column values with pattern matching – Javascript

I have a table cities with records like:

Name Value
id 62
name Alberta

There are many more but the format is the same. I have an API in Node.js in which I receive a city name. Now, the city name can be an exact match of the name column or a variation of it. Like it can be ‘Albarta’ or ‘Alberta City’, etc.

Is there any way I can return this row by an SQL query without using an external service like elascticsearch or similar? I tried like operator but it’s very limited. This query doesn’t return the mentioned (and required) record:

select * from cities c where c."name" like 'Albarta%'
Tags:

2

Answers


  1. 0

    You could have a look into the SIMILAR TO function in Postgres

    SELECT 'Albarta' SIMILAR TO '%Alb(e|a)rta%'

    https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-SIMILARTO-REGEXP

    Note: You might need to adjust the REGEX based on the values that you are looking for

    Login or Signup to reply.
  2. 0

    You can use soundex()

    For SQL Server:

    with cities as
(select 1 id,'Alberta' name)
select * from cities where soundex(name)=SOUNDEX('Albarta city')

    Soundex is a simple algorithm for comparing/normalizing English words based on phonetic similarity. PostgreSQL 8.3 and later include a soundex(text) function in the fuzzystrmatch extension.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search