Android Sql Like

Question

I'm using SQLite for my database and what I need is get the list of the username based on a specific string

My query is

SELECT * FROM username_table WHERE username like 'john[^0-9]%' 

Results are:

john1, johnny12, john123, johnatan12 ... (Except john I dont know why)

What i need is only john or john with any number besides him

Expected Result

john, john012, john1245, john1, john46...

Thank you

• hcarrasko: Using like 'john' will return Johnatan too
• Androyds: @Hector yep I only need John with with/wothout numbers

Answer 1

Have you tried using REGEXP operator? Following query works like a charm for data you've provided:

SELECT * FROM username_table WHERE (username REGEXP 'john[0-9]*');

Please note, it is case-sensitive.

Answer 2

This should work

 SELECT * FROM username_table WHERE username like 'john[^0-9]%' or username like 'john';

• Stack Exchange Broke The Law: @harpun "Not an answer" is for answers that aren't even attempts at answering the question.
• alsabsab: what is the problem with my answer @immibis
• Stack Exchange Broke The Law: @sabsab Nothing, another user flagged it as not an answer though.

Answer 3

Use this query

SELECT * FROM username_table WHERE username like 'john?[0-9]c/o'

• Androyds: it still the same it renders johnny01, johnatan12 etc
• Androyds: 0 Rows returned from: SELECT * FROM username_table WHERE username like 'john?[0-9]c/o'