CODEWITHSUNDEEP
sqlsqlite
Brandon Minton
Brandon Minton

Reputation: 1004

How to remove non-alphanumeric characters in a column?

Using sqlite3, I have a simple table like this:

ArtistID Name             
---------------------------------
0        andres segovia      
1        andres|segovia|       
2        ?andres-segovia        
3        andres segovia and john 
4        anya marina          
5        aphex twin          
6        aphex-twin

What would a select look like in order to see results like this:

ArtistID Name             
---------------------------------
0        andressegovia      
1        andressegovia       
2        andressegovia        
3        andressegoviaandjohn 
4        anyamarina          
5        aphextwin          
6        aphextwin

I've tried using the regex capability by including the line ".load /usr/lib/sqlite3/pcre.so" into my ~/.sqliterc file but using REGEXP in a query returns weird results ending in "Error: no string" so perhaps there is a clearer way?

Upvotes: 2

Views: 1948

Answers (1)

Marco
Marco

Reputation: 57583

You could try something like this

SELECT DISTINCT 
  replace(replace(replace(replace(name, 
    ' ', ''),
    '|', ''),
    '?', ''),
    '-', '') FROM your_table

Upvotes: 3

Related Questions