如何修剪通过选择查询的 in 子句的值 [英] How to trim the values passing the in clause of the select query

问题描述

下面是使用 in 条件选择记录的简单 sql 查询.

Below is simple sql query to select records using in condition.

--like this I have 6000 usernames
select * from tblUsers where Username in ('abc  ','xyz  ',' pqr  ',' mnop  ' );

我知道有 LTrim &Rtrim 在 sql 中删除前导的尾随空格 left &分别正确.

I know there are LTrim & Rtrim in sql to remove the leading trailing spaces form left & right respectively.

我想删除左边的空格 &就在我提供给选择查询的所有用户名中.

I want to remove the spaces from left & right in all the usernames that I am supplying to the select query.

注意:-

• 我想修剪我在 in 子句中传递的值.(我不想将 LTrim & RTrim 传递给每个传递的值).

• I want to trim the values that I am passing in the in clause.(I don't want to pass LTrim & RTrim to each value passed).

记录中没有尾随空格，但我在子句中传递的值是从 excel & 复制的.然后粘贴到 Visual Studio 中.然后使用 ALT 键我把 '(单引号)放在左边 &字符串的右侧.由于这个原因，一些字符串在右侧尾随有空格.

There are no trailing space in the records but value that I am passing in the clause is copied from excel & then pasted in Visual Studio. Then using ALT key I put '(single quote) at the left & right sides of the string. Due to this some strings has spaces in the right side trailing.

如何在select查询中使用trim函数?

How to use the trim function in the select query?

我使用的是 MS SQL Server 2012

I am using MS SQL Server 2012

推荐答案

如果我正确理解您的问题，您将从 Excel 粘贴到如下临时查询中的 IN 子句中.

If I understand your question correctly you are pasting from Excel into an IN clause in an adhoc query as below.

尾随空格无关紧要.它仍然会匹配字符串 foo 并且没有任何尾随空格.

The trailing spaces don't matter. It will still match the string foo without any trailing spaces.

但您需要确保没有前导空格.

But you need to ensure that there are no leading spaces.

既然数据的来源是Excel，为什么不直接在那里做呢?

As the source of the data is Excel why not just do it all there?

你可以使用公式

= CONCATENATE("'",TRIM(SUBSTITUTE(A1,"'","''")),"',")

然后复制结果(来自上面截图中的 B 列)，只需要从最后一个条目中剪掉多余的逗号.

Then copy the result (from column B in the screenshot above) and just need to trim off the extra comma from the final entry.

这篇关于如何修剪通过选择查询的 in 子句的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！