Here’s an example of returning rows that contain alphanumeric characters in SQLite.
Alphanumeric characters are alphabetic characters and numeric characters.
Example
Suppose we have a table with the following data:
SELECT c1 FROM t1;Result:
Music Live Music Café Café Del Mar 100 Cafés [email protected] 1 + 1 () !@#&()–[{}]:;',?/* `~$^+=<>“ $1.50 Player 456 007 null é É é 123 ø ø 123
We can use the REGEXP operator with a regular expression to return just those rows that contain alphanumeric characters only:
SELECT c1 FROM t1
WHERE c1 REGEXP '^[A-Za-z0-9]+$';Result:
Music 007
In this case, I didn’t include unicode characters like é, É, or ø in my range, and so any rows that contain those characters are excluded from the output.
You’ll need to ensure that you’ve got the relevant characters covered when doing this.
Here’s an example of expanding the range to include those characters:
SELECT c1 FROM t1
WHERE c1 REGEXP '^[A-Za-zÀ-Þß-ÿ0-9]+$';Result:
Music Café 007 é É ø
The space character is non-alphanumeric. Therefore, to return rows that include spaces, we can add a space to our regular expression:
SELECT c1 FROM t1
WHERE c1 REGEXP '^[A-Za-zÀ-Þß-ÿ0-9 ]+$';Result:
Music Live Music Café Café Del Mar 100 Cafés Player 456 007 é É é 123 ø ø 123
We can use the following code to include all rows that contain characters from our range (even if they also contain characters outside this range):
SELECT c1 FROM t1
WHERE c1 REGEXP '[A-Za-zÀ-Þß-ÿ0-9]';Result:
Music Live Music Café Café Del Mar 100 Cafés [email protected] 1 + 1 $1.50 Player 456 007 é É é 123 ø ø 123