string T-SQL：如何获取字符串的确切长度（以字符为单位）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3750377/
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
T-SQL: How to obtain the exact length of a string in characters?
提问by Fabian Schmied
I'm generating T-SQL SELECT statements for tables for which I have no data type information up-front. In these statements, I need to perform string manipulation operations that depend on the length of the original value of the tables' columns.
我正在为我没有预先提供数据类型信息的表生成 T-SQL SELECT 语句。在这些语句中，我需要执行依赖于表列原始值长度的字符串操作操作。
One example (but not the only one) is to insert some text at a specific position in a string, including the option to insert it at the end:
一个例子（但不是唯一的）是在字符串的特定位置插入一些文本，包括在末尾插入它的选项：
SELECT
CASE WHEN (LEN ([t0].[Product] = 8)
THEN [t0].[Product] + 'test'
ELSE STUFF ([t0].[Product], 8, 0, 'test')
END
FROM [OrderItem] [t0]
(The CASE WHEN + LEN is required because STUFF doesn't allow me to insert text at the end of a string.)
（CASE WHEN + LEN 是必需的，因为 STUFF 不允许我在字符串的末尾插入文本。）
The problem is that LEN excludes trailing blanks, which will ruin the calculation. I know I can use DATALENGTH, which does not exclude trailing blanks, but I can't convert the bytes returned by DATALENGTH to the characters required by STUFF because I don't know whether the Product column is of type varchar or nvarchar.
问题是 LEN 排除了尾随空白，这会破坏计算。我知道我可以使用不排除尾随空格的 DATALENGTH，但我无法将 DATALENGTH 返回的字节转换为 STUFF 所需的字符，因为我不知道 Product 列的类型是 varchar 还是 nvarchar。
So, how can I generate a SQL statement that depends on the exact length of a string in characters without up-front information about the string data type being used?
那么，如何生成一个 SQL 语句，该语句取决于字符串的确切长度（以字符为单位），而无需预先提供有关所用字符串数据类型的信息？
回答by Fabian Schmied
Here's what I ended up using:
这是我最终使用的：
SELECT
CASE WHEN ((LEN ([t0].[Product] + '#') - 1) = 8)
THEN [t0].[Product] + 'test'
ELSE STUFF ([t0].[Product], 8, 0, 'test')
END
FROM [OrderItem] [t0]
Measurements indicate that the LEN (... + '#') - 1 trick is about the same speed as LEN (...) alone.
测量结果表明 LEN (... + '#') - 1 把戏与单独的 LEN (...) 的速度大致相同。
Thanks for all the good answers!
感谢所有的好答案！
回答by ibram
try this:
尝试这个：
SELECT
CASE WHEN (LEN (REPLACE([t0].[Product],' ', '#') = 8)
THEN [t0].[Product] + 'test'
ELSE STUFF ([t0].[Product], 8, 0, 'test')
END
FROM [OrderItem] [t0]
回答by Martin Smith
Can't you look up the type information for the columns in the system tables?
你不能在系统表中查找列的类型信息吗？
If not then to determine whether or not a column is varcharor nvarcharthis would do it.
如果不是，那么确定一列是否是varchar或nvarchar这将做到这一点。
create table #test
(
c varchar(50),
n nvarchar(50)
)
insert into #test values ('1,2,3,4 ',N'1,2,3,4,5 ')
SELECT
CASE
WHEN datalength(CAST(c AS nvarchar(MAX))) = datalength(c)
THEN 'c is nvarchar'
ELSE 'c is char'
END,
CASE
WHEN datalength(CAST(n AS nvarchar(MAX))) = datalength(n)
THEN 'n is nvarchar'
ELSE 'n is char'
END
FROM #test
回答by Peter Radocchia
Use DATALENGTHand SQL_VARIANT_PROPERTY:
使用DATALENGTH和SQL_VARIANT_PROPERTY：
SELECT
CASE
WHEN 8
= DATALENGTH([t0].[Product])
/ CASE SQL_VARIANT_PROPERTY([t0].[Product],'BaseType') WHEN 'nvarchar' THEN 2 ELSE 1 END
THEN [t0].[Product] + 'test'
ELSE STUFF ([t0].[Product], 8, 0, 'test')
END
FROM [OrderItem] [t0]
回答by John Fouhy
If there are no leading blanks, len(reverse(column_name))will give you the column length.
如果没有前导空格，len(reverse(column_name))将为您提供列长度。
