SQL 如何在Sql Server中检查字符串长度然后选择子字符串

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/15091952/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-01 13:55:47  来源:igfitidea点击:

How to check string length and then select substring in Sql Server

sqlsql-server-2008tsql

提问by Code Rider

In a view, i have a column commentswhich may contain large string. I just want to select first 60 characters and append the '...' at the end of the selected string.

在一个视图中,我有一个可能包含大字符串的列注释。我只想选择前 60 个字符并在所选字符串的末尾附加 ' ...'。

For selecting first 60 characters i have used following query:

为了选择前 60 个字符,我使用了以下查询:

select LEFT(comments, 60) as comments from myview

Now i want its processing as below:

现在我想要它的处理如下:

  1. Check it contains more then 60 characters or not.
  2. If contains then select only first 6o characters and append three dots at the end.
  3. If it doesn't contain more then 60 characters then select whole string without appending three dots at the end.
  1. 检查它是否包含超过 60 个字符。
  2. 如果包含则仅选择前 6o 个字符并在末尾附加三个点。
  3. 如果它不包含超过 60 个字符,则选择整个字符串而不在末尾附加三个点。

Thanks

谢谢

回答by John Woo

To conditionally check the length of the string, use CASE.

要有条件地检查字符串的长度,请使用CASE.

SELECT  CASE WHEN LEN(comments) <= 60 
             THEN comments
             ELSE LEFT(comments, 60) + '...'
        END  As Comments
FROM    myView