CODEWITHSUNDEEP
sqlstringsqliteselectsubstring
Béa
Béa

Reputation: 121

Search a substring with sql query

In my SQLite database I have a column with a series of values like #7 qsqjjsqdzdsql. qsqjjsqdzdsql is variable. The column type is TEXT. I want to extract the number from all rows, and then get the largest one.

First I thought the best solution is to do that with the substring function but the length of the number can vary. Maybe regexp, but I cannot find a suitable pattern (it begins with #[1-9]space)

EDIT:

For example, I have 4 rows in a column

#7 qslqsendds
#78 mdsldpedd
#3 dmsdlef
#6 sdefzmdsdos

I want to get only the largest number. For example, here it is 78. I want 78 (not #78, not 78 mdsldpedd).

Upvotes: 0

Views: 638

Answers (2)

forpas
forpas

Reputation: 164089

All you have to do is get rid of the leading '#' with SUBSTR(col, 2).
SQLite will convert implicitly a string like '7 qslqsendds' to a numeric 7 simply by adding 0:

SELECT MAX(SUBSTR(col, 2) + 0) AS max_col
FROM tablename

See the demo.

Upvotes: 1

Mureinik
Mureinik

Reputation: 311163

You could use instr to find the position of the space, and then use substr to extract the number before it. Then you could cast it to an int, and query its max:

SELECT MAX(CAST(SUBSTR(col1, 2, INSTR(col1, ' ') - 1) AS INT))
FROM table1

DBFiddle demo

Upvotes: 2

Related Questions