Can you check if a field has NO numeric values in the query?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Can you check if a field has NO numeric values in the query?

Post by simonmlewis »

I have a field that contains things like this: "433|211|309|", but sometimes it will be "||||", as occasionally when removing the numbers, it leaves some ||| behind.

This isn't great, but is there a way in the SELECT *... query where I can say "where field only contains numeric"??
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Can you check if a field has NO numeric values in the qu

Post by simonmlewis »

Heavens I'm making this difficult - just "where field > 0".
If it's 311|233|.... it comes up with one entry. If it's "|||", it comes up empty.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
desheikh
Forum Newbie
Posts: 1
Joined: Tue Jan 20, 2015 5:35 am

Re: Can you check if a field has NO numeric values in the qu

Post by desheikh »

as occasionally when removing the numbers, it leaves some ?
omair
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Can you check if a field has NO numeric values in the qu

Post by requinix »

Sounds like you should fix your database so you aren't storing multiple values in one column.

But to answer the question, MySQL has REGEXP so you can search for anything not matching "[0-9]".
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: Can you check if a field has NO numeric values in the qu

Post by twinedev »

While you have a "fix" to make it work, it is good to eliminate the issue before it reaches the database.

Whenever you write the value to the database, do trim( $variable, '|' ) so you get rid of any possible leading/tailing pipes, and if it is only pipes, you will get empty string.
Post Reply