CODEWITHSUNDEEP
sqlsqlite
ΩlostA
ΩlostA

Reputation: 2601

SQLite: How to extract only numbers and special characters from column?

I try to extract the numbers and the special characters in a string, how can I do that in SQL? (SQLite)

Is there a way with FORMAT()? with REPLACE()?
I search for the [@$!%*?&/] and the [0-9] numbers...

String: 

Rue des cocotiers, 9/11

Result: 

9/11

Thanks

Upvotes: 0

Views: 3262

Answers (1)

Noob G
Noob G

Reputation: 46

This would generally be a regex solution, but although sqlite now has the ability to run REGEXP expressions in WHERE clauses, it has not implemented the standard regexp_replace function found in more comprehensive SQL implementations.

How to use REGEXP in SQLite3 to extract matched string (as a column value)

To run an expression like this:

SELECT trim(regexp_replace(threeload.title, '\b([\d*#+\/]+)\b')) str
  FROM threeload
 WHERE threeload.title REGEXP '\b([\d*#+\/]+)\b';

you would first need to recompile sqlite with an additional function, perhaps like the one outlined in this answer:

https://stackoverflow.com/a/44586265/10890850

Here are the steps in case it gets deleted (not my answer, please give the original response any votes):

Sqlite by default does not provide regex_replace function. You need to load it as an extension. Here is how i managed to do it.

Download this C code for the extension (icu_replace)

Compile it using

gcc --shared -fPIC -I sqlite-autoconf-3071100 icu_replace.c -o icu_replace.so

And in sqlite3 runn following command post above mentioned command has run and create a file icu_replace.so

SELECT load_extension(' path to icu_replace.so',
'sqlite3_extension_init') from dual;

After this you will be able to use such a function as :-

select regex_replace('\bThe\b',x,'M') from dual;

Upvotes: 3

Related Questions