Geek Answers Handbook

Postgresql search using only alphanumeric characters

It is quite simple to strip non-alphanumeric characters from a search term, but how do you compare it to only non-alphanumeric characters in a database?

For example, if I search for stack's , how can I make it match both stacks and stack's ?

What do I need to do with the what-do-i-do variable below to do it higher?

 SELECT * FROM table WHERE <what-do-i-do> ilike 'stacks'
+6
source share
2 answers

One way to do this is to translate:

 select * from table where translate(lower(WhatIDo), translate(lower(WhatIDo), 'abcdefghijklmnopqrstuvwxyz', ''), '') = 'stacks'

The internal translation finds all non-absolute characters. The external ones then remove them from the string.

+4
source

You can try replacing all instances of non-alphanumeric characters with a wildcard character ('%'). In your example:

 SELECT * FROM table WHERE data like 'stack%s'
0
source

More articles:


All Articles