如何在SQL查询中使用Left（）时删除最后一个单词 [英] How to Remove the last word while using Left() in sql query

问题描述

如何在sql查询中使用left时删除最后一个单词...我不想在这样做时避免破碎的单词...

我的查询如下：

 选择  top   2  tempJobTitle.id，tempJobTitle.JobRefId，tempJobTitle.JobTitle， LEFT （jobmaster.JobDescription2， 330 ）+ '  ....'  as  JobDescription，jobmaster.PostedDate 
 来自 tempJobTitle  inner  加入 jobmaster   jobmaster.JobRefId = tempJobTitle.JobRefId 
 其中​​ tempJobTitle .Status = ' 已批准' 和 jobmaster.ClosingDate> = '  2013-03-01' 订单   tempJobTitle.DateofEntry  desc  

现在我得到的最后一句话有时会被打破..想要通过sql查询删除最后一个字...

解决方案

1。您可以将自己的函数添加到SQL Server数据库，以将JobDescription2列值作为参数并返回截断的版本。为此，您将编写一个Transact-SQL函数，该函数将从右向左索引，在传递的参数中查找空格，直到找到空格的索引小于所需长度。



2.您可以将VB.NET或C＃.NET标量值函数程序集添加到数据库，该函数对作为参数传递的JobDescription2值执行类似的算法。请参阅 CLR标量值函数 [ ^ ]



3.你可以在使用类似算法将数据返回到您的应用程序后，在VB.NET或C＃.NET中进行截断。这方面的例子如下：



VB示例

 ' 注意：以下行是测试数据。它应该全部在一行 
  Dim  strJobDescription2  As  字符串 =   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 
   strJobDescription2.Length>  330  
 strJobDescription2 = strJobDescription2.TrimEnd.Substring（ 0 ，_ 
 strJobDescription2。 TrimEnd.LastIndexOf（ ））。TrimEnd 
 结束   
 Console.Write（strJobDescription2.Length&  ：& strJobDescription2）

C＃示例

 //注意：以下行是测试数据。它应该全部在一行
 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）; 

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:

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...

解决方案

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

' 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);

这篇关于如何在SQL查询中使用Left（）时删除最后一个单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！