Pulling from a list

[junkjones]

Hi Everyone! Please forgive my ignorance - I am a ColdFusion programmer who does not know much about databases and is trying to incorporate MySQL into my programming with not much luck.

I have a field in my db that is a list of numbers. ("1,13,9,47" etc.) What I want to do is write a select query and take the last number in the list to query against (do I make any sense??)

Here's what my code looks like:

select * from templates
where numberlist = 47

This doesn't work, of course, because the value for numberlist is "1,13,9,47", so it doesn't match. I want to access the LAST number in the list - is there a syntax to do that? In Coldfusion it's as easy as #ListLast(numberlist)# (this would return the value 47) - I'm hoping it's this easy in SQL too.

Thanks in advance!!!

 

[piti]

i think this will do what you need

select * from templates where SUBSTRING_INDEX(numberlist, ',', -1)=47

more info here

http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#IDX1197

 

[DumTech]

Not familiar with the above SQL (about to go familiarize myself...), but it looks like a good solution.

In a similar situation, I used explode(',',$list_string) with a list ('x,y,z,n') the other night to make it an array so I could compare a value to each number in the list.

Quite the newbie myself, so that might not even be a great way to do that, but it worked. In case you need to do more with that list, it could come in handy...

 

[junkjones]

That seemed to do the trick! I did not try the second suggestion, as the first one worked - but thank you both very much for your replies