How to use LIKE for detecting numbers with commas?

--000e0cd32a60724dd8046e1397a4
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Hi,
I have numbers separated with commas saved into a TEXT Field, for example:

ROW1: 10,5,2,8,
ROW2: 2,7,9,65
ROW3: 99,100,55,10,88,
etc...


Now i want to make a query like this:
SELECT * FROM table where numbers LIKE '%8%';

The above query when executed returned the following:
ROW1: 10,5,2,8,
ROW3: 99,100,55,10,88,

Where it should only return ROW1:
ROW1: 10,5,2,8,

But it is also detecting '88' from ROW2.

Any solution to this?
I only want exact numbers to be searched out.


Thanks!



---
http://www.visualbooks.com.pk/

--000e0cd32a60724dd8046e1397a4--

Re: How to use LIKE for detecting numbers with commas?

LIKE '%,8,%' ?

Probably not as elegant as you were looking for, but it works :)

Colin

On Monday 06 July 2009 21:31:51 Highviews wrote:
> Hi,
> I have numbers separated with commas saved into a TEXT Field, for example:
>
> ROW1: 10,5,2,8,
> ROW2: 2,7,9,65
> ROW3: 99,100,55,10,88,
> etc...
>
>
> Now i want to make a query like this:
> SELECT * FROM table where numbers LIKE '%8%';
>
> The above query when executed returned the following:
> ROW1: 10,5,2,8,
> ROW3: 99,100,55,10,88,
>
> Where it should only return ROW1:
> ROW1: 10,5,2,8,
>
> But it is also detecting '88' from ROW2.
>
> Any solution to this?
> I only want exact numbers to be searched out.
>
>
> Thanks!
>
>
>
> ---
> http://www.visualbooks.com.pk/



--
Your love life will be... interesting.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: How to use LIKE for detecting numbers with commas?

It's ugly, but this should work:

SELECT * FROM table where numbers LIKE '8,%' or numbers LIKE '%,8,%' or
numbers LIKE '%,8'

-- B


----- Original Message -----
From: "Highviews"
To:
Sent: Monday, July 06, 2009 6:31 PM
Subject: How to use LIKE for detecting numbers with commas?


> Hi,
> I have numbers separated with commas saved into a TEXT Field, for example:
>
> ROW1: 10,5,2,8,
> ROW2: 2,7,9,65
> ROW3: 99,100,55,10,88,
> etc...
>
>
> Now i want to make a query like this:
> SELECT * FROM table where numbers LIKE '%8%';
>
> The above query when executed returned the following:
> ROW1: 10,5,2,8,
> ROW3: 99,100,55,10,88,
>
> Where it should only return ROW1:
> ROW1: 10,5,2,8,
>
> But it is also detecting '88' from ROW2.
>
> Any solution to this?
> I only want exact numbers to be searched out.
>
>
> Thanks!
>
>
>
> ---
> http://www.visualbooks.com.pk/
>



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: How to use LIKE for detecting numbers with commas?

In the last episode (Jul 06), avrombay@whyisitthat.com said:
> From: "Highviews"
> > I have numbers separated with commas saved into a TEXT Field, for
> > example:
> >
> > ROW1: 10,5,2,8,
> > ROW2: 2,7,9,65
> > ROW3: 99,100,55,10,88,
> > etc...
> >
> > Now i want to make a query like this:
> > SELECT * FROM table where numbers LIKE '%8%';
> >
> > Any solution to this?
> > I only want exact numbers to be searched out.
>
> It's ugly, but this should work:
>
> SELECT * FROM table where numbers LIKE '8,%' or numbers LIKE '%,8,%' or
> numbers LIKE '%,8'

Even better:

SELECT * FROM table WHERE find_in_set('8',numbers);

http://dev.mysql.com/doc/refman/5.1/en/string-functions.html #function_find-in-set


--
Dan Nelson
dnelson@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: How to use LIKE for detecting numbers with commas?

--000e0cd2e2d2a970c5046e22db44
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Thats Great!

Thanks a Ton!


:)




On Mon, Jul 6, 2009 at 7:55 PM, Dan Nelson wrote:

> In the last episode (Jul 06), avrombay@whyisitthat.com said:
> > From: "Highviews"
> > > I have numbers separated with commas saved into a TEXT Field, for
> > > example:
> > >
> > > ROW1: 10,5,2,8,
> > > ROW2: 2,7,9,65
> > > ROW3: 99,100,55,10,88,
> > > etc...
> > >
> > > Now i want to make a query like this:
> > > SELECT * FROM table where numbers LIKE '%8%';
> > >
> > > Any solution to this?
> > > I only want exact numbers to be searched out.
> >
> > It's ugly, but this should work:
> >
> > SELECT * FROM table where numbers LIKE '8,%' or numbers LIKE '%,8,%' or
> > numbers LIKE '%,8'
>
> Even better:
>
> SELECT * FROM table WHERE find_in_set('8',numbers);
>
>
> http://dev.mysql.com/doc/refman/5.1/en/string-functions.html #function_find-in-set
>
>
> --
> Dan Nelson
> dnelson@allantgroup.com
>

--000e0cd2e2d2a970c5046e22db44--