Alternative for LEFT() and CHARINDEX() that works with text Data Type

I have a stored procedure that receives a list of ids, to get the emails of each of those ids. The problem that I'm having is that I'm using a char data type that is max length is 8000, but the contact lists are getting bigger and soon they might reach the variable max length. The contact list that I receive will look something like this "1234,67523,67875,789687,", I'm using "," as a separator. Right now what I do is this

@.array_value=LEFT(@.ContactList, @.separator_position- 1)

The LEFT function doesn't work with data types text and ntext. But I'm in need of a string data type with a max length bigger than 8000. So I will apreciate if anyone knows of another function that does the same or similar and works with text data type, or any other ideas will be welcome.

Thanks,

Erick

what database & version are you using?

|||

SQL Server 2005

|||

Use varchar(max) instead of varchar(8000) then.

|||

Can you change the datatype? Try using varchar(MAX) or nvarchar(MAX). MSDN says built-in string functions have been built to work with the new large object types (MAX).

http://msdn2.microsoft.com/en-us/library/ms178158.aspx

|||

I just changed, I didn't know that worked. I just read thats new in sql 2005, that didn't work on 2000.

Thanks

|||

That worked but, Can I give max value to varchar in asp.net vb?

|||

http://msdn2.microsoft.com/en-us/library/a1904w6t(VS.80).aspx

|||

It looks like you just set the type to the normal DBType, i.e. use varchar for varchar(MAX), then set the length to -1 (this tells the parameter to use whatever length the data is, rather than specifying how much of the data to use).