Click here to Skip to main content
14,934,919 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
How to Remove the last word while using left in sql query...I weant to avoid broken words while doing so...
My query as follows:
SQL
select  top 2 tempJobTitle.id,tempJobTitle.JobRefId,tempJobTitle.JobTitle,LEFT (jobmaster.JobDescription2, 330)+'....' as JobDescription,jobmaster.PostedDate
from tempJobTitle inner join  jobmaster on jobmaster.JobRefId=tempJobTitle.JobRefId
where tempJobTitle.Status='Approved' and jobmaster.ClosingDate >= '2013-03-01' order by tempJobTitle.DateofEntry desc


Right now i am getting last word broken at times..Want to remove that last word via sql query...
Posted
Updated 7-Mar-13 9:12am
v4
Comments
joshrduncan2012 7-Mar-13 14:47pm
   
And? What's your question?
Maciej Los 7-Mar-13 15:05pm
   
This is not well framed question. Please be more specific and provide more details (put examples).

1 solution

1. You could add your own Function to the SQL Server database to take the JobDescription2 column value as a parameter and return the truncated version. To do so, you would write a Transact-SQL Function that would index from the right to the left, looking for a space in the passed parameter until the index where you find a space is less than the desired length.

2. You could add a VB.NET or C#.NET scalar-valued function assembly to the database that performs a similar algorithm on the JobDescription2 value passed as a parameter. See CLR Scalar-Valued Functions[^]

3. You can do the truncation in VB.NET or C#.NET after the data has been returned to your application using a similar algorithm. Examples of this are below:

VB Example
VB
' Note: Following line is test data. It should be all on one line
Dim strJobDescription2 As String = "ABC DEF GHI JKL MNO PQR STU VWX YZABC DEF GHI JKL MNO PQR STU VWX YZABC DEF GHI JKL MNO PQR STU VWX YZABC DEF GHI JKL MNO PQR STU VWX YZABC DEF GHI JKL MNO PQR STU VWX YZABC DEF GHI JKL MNO PQR STU VWX YZABC DEF GHI JKL MNO PQR STU VWX YZABC DEF GHI JKL MNO PQR STU VWX YZABC DEF GHI JKL MNO PQR STU VWX YZABC DEF GHI JKL MNO PQR STU VWX YZABC DEF GHI JKL MNO PQR STU VWX YZ"
While strJobDescription2.Length > 330
    strJobDescription2 = strJobDescription2.TrimEnd.Substring(0, _
        strJobDescription2.TrimEnd.LastIndexOf(" ")).TrimEnd
End While
Console.Write(strJobDescription2.Length & ": " & strJobDescription2)


C# Example
// Note: Following line is test data. It should be all on one line
string strJobDescription2 = "ABC DEF GHI JKL MNO PQR STU VWX YZABC DEF GHI JKL MNO PQR STU VWX YZABC DEF GHI JKL MNO PQR STU VWX YZABC DEF GHI JKL MNO PQR STU VWX YZABC DEF GHI JKL MNO PQR STU VWX YZABC DEF GHI JKL MNO PQR STU VWX YZABC DEF GHI JKL MNO PQR STU VWX YZABC DEF GHI JKL MNO PQR STU VWX YZABC DEF GHI JKL MNO PQR STU VWX YZABC DEF GHI JKL MNO PQR STU VWX YZABC DEF GHI JKL MNO PQR STU VWX YZ";
while (strJobDescription2.Length > 330) {
	strJobDescription2 = strJobDescription2.TrimEnd().Substring(0, 
            strJobDescription2.TrimEnd().LastIndexOf(" ")).TrimEnd();
}
Console.Write(strJobDescription2.Length + ": " + strJobDescription2);
   
v5

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900