How to select only last 500 words from column of DataTable and update it in the same DataTable in C#

Question

1.44/5 (2 votes)

See more:

I'm calling the DB on C# Form in a DataTable, it has a column with Text data which more than 3K-4K words but I only need last 500 words from that column and the column index is 6th so how to update only last 500 words in that column?

I tried something like this:

C#

SqlDataAdapter da = new SqlDataAdapter("select * from Table1", sqlCon);
DataTable dtMainSQLData = new DataTable();
                if (dtMainSQLData.Columns.Contains("DA_ArticleDetails"))
                {
                    //not sure what to code here
                    //but the code should select last 500 words from the column
                    //and update them in the same column
               }
//fill the data table with the data from SELECT query
 da.Fill(dtMainSQLData);

Help Please!!

Posted 23-Jul-14 0:00am

GoneBump

Updated 23-Jul-14 0:28am

v3

Add a Solution

Comments

Varun Sareen 23-Jul-14 6:13am

what have you tried so far?

GoneBump 23-Jul-14 6:25am

I updated the question. Could you please look at it.

Maciej Los 23-Jul-14 6:28am

What you mean by "last 500 words"?

GoneBump 23-Jul-14 6:30am

This column 'DA_ArticleDetails' contains unwanted text record, mainly more that 3K-4K words and the usefull text is at the end of the every cell in the column.

Maciej Los 23-Jul-14 6:34am

OK, is there any specific order? How to recognize which is last?

GoneBump 23-Jul-14 6:46am

First, need to find the column from the DataTable then for every row of it iterate to the end then apply
for(endOfRow, iterate till 500 words, decrement)
{
select the data; update it in the row;
}
would help to get the last/end of the row and to save it.

Ajith K Gatty 23-Jul-14 6:48am

hello GoneBump,
I think you are binding this datatable to some gridview. Then you can use few words in a particular column and you can provide a tooltip to get full details.
According to me you shouldn't modify the result what you are getting from the query. it would be better if you modify the result in the control where you are gonna bind this table.

GoneBump 24-Jul-14 4:44am

The following script does helped me:
UPDATE table1
SET [DA_ArticleDetails] =RIGHT([DA_ArticleDetails], 6000)

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

krishnaMurali · Accepted Answer · 2014-07-23T01:50:00

This is something that suits to you:

SQL

Create FUNCTION [dbo].[fnGetWords](@pStr varchar(max), @pWords int)
RETURNS VARCHAR(MAX)
AS
BEGIN
     DECLARE @lPos int, @lCtr int
     SET @lCtr = Len(@pStr)
     SET @lPos = 0
     WHILE @lCtr>=@pWords
     BEGIN
          SET @lPos = CHARINDEX(' ',  @pStr, @lPos + 1)
          SET @lCtr = @lCtr - 1
     END
     RETURN SUBSTRING(@pStr,@lPos, Len(@pStr)-1)
END


select dbo.fnGetWords('This is getting words from right',-2)

gives the last word "right". you can change the logic as per your criteria.

arry.net · Accepted Answer · 2014-07-23T00:48:00

Solution 1

Piece of cake
select RIGHT(colname, 500)from Table1

You can also use Left ,
Note : Both of them won't throw any error even if there is not enough data available.

Posted 23-Jul-14 0:48am

arry.net

Updated 23-Jul-14 0:50am

v2

Comments

Maciej Los 23-Jul-14 6:50am

500 words, not signs in a single row!

GoneBump 23-Jul-14 8:48am

Thanks for the Solution. It helped in some way but it is giving 500 characters and not words.

GoneBump 23-Jul-14 9:37am

I tried something like this:

UPDATE table1
SET [DA_ArticleDetails] =
(
select RIGHT([DA_ArticleDetails], 8000)
from table1
)

but it shows a error:

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Could you help?

arry.net 27-May-15 8:06am

your query return more then one row, try top 1 or add a where clause
select RIGHT([DA_ArticleDetails], 8000)
from table1