get x # of characters in string...

[Golgotha]

is there a way via SQL to say get me the first x number of characters from a field?

no point in getting all 1000+ characters if I am only going to show the first 100 of them...

[dhtmlgod]

Theres the SUBSTRING function:

 USE pubs
 SELECT au_lname, SUBSTRING(au_fname, 1, 1)
 FROM authors
 ORDER BY au_lname

[Golgotha]

what's the USE part do?

also once you do this can you still refer to the field au_fname ?

string strSelect = "USER_MESSAGE_LIST.ListID, USER_MESSAGE_LIST.UserID, SUBSTRING(USER_MESSAGE.MessageText,1,25), USER_MESSAGE.AlertLevel";

string strFrom = "USER_MESSAGE INNER JOIN USER_MESSAGE_LIST ON USER_MESSAGE.MessageID = USER_MESSAGE_LIST.MessageID";

string strWhere = "USER_MESSAGE_LIST.UserID =" + Session["UserID"]; //messages for that user only

String strQuery = string.Format("SELECT {0} FROM {1} WHERE {2}", strSelect, strFrom, strWhere);

I get this exception: A field or property with the name 'MessageText' was not found on the selected datasource.

but, I don't have the use in there....

[dhtmlgod]

Are you using SQL Server?

[Golgotha]

yeah

[dhtmlgod]

Open up Enterprise Manager and click F1. The search for SUBSTRING



The code I posted from straight from the SQL Server help file.

Also, try this:

string strSelect = "USER_MESSAGE_LIST.ListID, USER_MESSAGE_LIST.UserID, SUBSTRING(USER_MESSAGE.MessageText,1,25) As MessageText, USER_MESSAGE.AlertLevel";
 

[Golgotha]

hahaha, exactly what I was just going to tell you. Thanks man

[Golgotha]

and now the next question is can you tell it not to break right in the middle of a word?

[vgarcia]

Off Topic:

Moved to the database forum since it's a SQL question.

[null]

why not just use this??...

select LEFT(myField, 100) from myTable

...where "myField" is the name of the field, "100" is the length of characters you want to return (can be any number, 1, 9 , 1007, I just used 100 for your case). The LEFT starts counting from the left side of the field (or in most cases, the "beginning" of the string), likewise, you can also use RIGHT if you want to start attacking from the rear of the field.