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

问题描述

下面是一个简单的sql查询，用于使用in条件来选择记录.

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&在sql中Rtrim删除左&前面的尾随空格分别对.

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.

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

我正在使用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屋！