Get last number in a list? 1

[countdrak]

I have a db with a column that has a list as a data. So something like this.


-1,121 ( I want 121
-1,121,1223 1223
-1,121,1 1)

I want the the last number from the list? Is there like a ListLast function in SQL? I am using SQL Server 2000.
I tried doing something like this, but kinda didnt work.

SELECT RIGHT(list,(CHARINDEX(',', list)))
from table

Any help will be appreciated.

 

[JarlH]

Lists of data stored in a table...? Consider redesigning your data model. Typically you create another table where you store each list item in a separate row.

BTW, there are no 'list functions' in SQL.

 

[countdrak]

There are parent-child relationships stored in the table. I was following the adjacency model.

This might help you.

http://www.sqlteam.com/item.asp?ItemID=8866

Anyways, thanks for the reply.

 

[r937]

countdrak, that article clearly shows the lineage being created in a loop

why wouldn't you therefore also use a loop to "unstring" the list and find the last item?



okay, having given you the mandatory "that's what you get for using data that's not even in first normal form" comment, i will now add that you can pull out the last item using the sql server functions RIGHT, REVERSE, and CHARINDEX

select case when charindex(',',thelist)=0
            then thelist
            else right(thelist,
                   charindex(','
                     ,reverse(thelist))-1)
        end as lastitem
 from yourtable

rudy

http://r937.com/

SQL Consulting

 

[countdrak]

Thanks man, I know I know 1NF

Eliminate duplicative columns from the same table.

Create separate tables for each group of related data and identify each row with a unique column (the primary key).



What can I say I in too deep to make changes... So thanks for the solution.