SQLite: Get only character string from text column

Question

I have the following table:

Table: Test1

Text_Column
------------
2021AB022
1A012998F
ZAY
212P09990
1232124
PMP

Expected result is:

Only_text
----------
ZAY
PMP

Try:

SELECT Text_Column AS Only_text
FROM Test1
WHERE Text_Column REGEXP '(A,Z)'

score 1 · Accepted Answer · answered Aug 23 '19 at 11:22

1

If your version of SQLite in fact supports REGEXP, then you were on the right track:

SELECT Text_Column AS Only_text
FROM Test1
WHERE Text_Column REGEXP '^[A-Z]+$';

But not all versions of SQLite would ship with REGEXP out of the box. Here is another SO question which discusses other ways to detect a number.

answered Aug 23 '19 at 11:22

Tim Biegeleisen

Okay, Can you tell me what will be the wildcard for not equal to only characters? Just opposite of what you did. – MAK Aug 23 '19 at 11:30
1

Use `NOT REGEXP '^[A-Z]+$` or maybe `REGEXP '[^A-Z]'` – Tim Biegeleisen Aug 23 '19 at 11:34

score 0 · Answer 2 · answered Aug 23 '19 at 11:37

0

I am not sure that this solves your problem, but works for the given example:

SELECT * FROM Test1 WHERE
Text_Column GLOB '*[^0-9]*';

giving back only the columns not containig characters in the range 0-9.

answered Aug 23 '19 at 11:37

g_bor

2 Answers2