SQLITE select only numeric values from text field

Question

In VB .net I'm wanting to run an sqlite select statement to fetch only numeric values from a text field (a4) which contains both numbers and letters.

My sqlite text a4 field data contains data like this:

  28 days
  1966
  the 100 years war

In would like to be able to fetch

  28
  1966
  100

The sqlite a4 field has a text data type

This is what I have tried

SELECT a4 from data where CAST(a4 AS INTEGER) GLOB '[0-9]'
SELECT a4 FROM data WHERE a4 REGEXP '[0-9]'

I have search for an answer but only found MS SQL answer that uses ISNUMERIC which doesn't exist in SQLITE.

Thanks

Maybe in the close but no cigar category: trim(lower(info),"abcdefghijklmnopqrstuvwxyz "). I guess you could include special characters too. But it wouldn't work on 2 plus 2 is 4 for instance. — DinoCoderSaurus
– DinoCoderSaurus, Commented Apr 1, 2019 at 22:03

Nick Lansley · Accepted Answer · 2019-08-24 12:50:02Z

3

You can do it in SQL for SQLite3:

SELECT ROUND(a4) from data WHERE NOT ROUND(a4) = 0;

This is because non-numeric values applied to the ROUND() function result in a value of 0. For example:

sqlite> select round('hello');
round('hello')
--------------
0.0

..whereas numeric values stored as text become numbers:

sqlite> select round('1234');
round('1234')
-------------
1234.0

N.B. There is a limitation to this solution is if a numeric value is also '0' or rounds to 0.0, such as text value '0.1'. It also will give a false positive if the text starts with a number but includes other characters, e.g.

sqlite> select round('1234Hello');
round('1234Hello')
------------------
1234.0

answered Aug 24, 2019 at 12:50

Nick Lansley

313 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mary · Accepted Answer · 2019-04-02 05:25:37Z

1

Not done in the database but in the client code.

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

    Dim strings() As String = {"28 days", "1966", "the 100 years war"}
    Dim numbers As New List(Of String)
    For Each s In strings
        Dim num As String = ""
        For Each c As Char In s
            If Char.IsNumber(c) Then
                num &= c
            End If
        Next
        numbers.Add(num)
    Next
    For Each num In numbers
        Debug.Print(num)
    Next
    '28
    '1966
    '100
End Sub

answered Apr 2, 2019 at 5:25

Mary

15.1k3 gold badges20 silver badges27 bronze badges

1 Comment

Mark Over a year ago

Thanks Mary, I might have to revert to this if I cannot achieve something similar using sql.

Collectives™ on Stack Overflow

SQLITE select only numeric values from text field

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related