如何在SQL中对varchar字段进行排序？ [英] How to sort varchar field in SQL?

问题描述

如何在SQL中对varchar字段进行排序？

我的数据为CTD-01 ..... CTD-35。我想要排序相同。作为varchar，我无法做同样的事情。

解决方案

ORDER BY子句适用于任何类型，包括NVARCHAR和VARCHAR，因此只是添加子句的情况。

但是......如果你正在查看不带有前导零的不同长度数值的值，那么排序顺序将不是你所期望的。

CTD-01到CTD-35将正常工作，但CTD-100不会在列表的末尾。

如果你想将数值视为数字，那么我建议您设置字段，一个是前导字母字符，第二个是整数，并使用计算字段生成组合值： https://msdn.microsoft.com/en-GB/library/ms188300.aspx?f=255&MSPPError=-2147217396 [ ^ ]

How to sort varchar field in SQL?
I am having data as CTD-01.....CTD-35. I want to sort the same. Being varchar I am unable to do the same.

解决方案

The ORDER BY clause works with any type including NVARCHAR and VARCHAR, so it's just a case of adding your clause.
But...if you are looking at values with different length numerics that aren't padded with leading zeros the sort order won't be what you expect.
"CTD-01" to "CTD-35" will work fine, but "CTD-100" will not be at the end of the list.
If you want to treat numeric values as numbers, then I'd suggest you set up to fields, one the leading alpha characters, the second an integer, and use a computed field to generate the "combined" value: https://msdn.microsoft.com/en-GB/library/ms188300.aspx?f=255&MSPPError=-2147217396[^]

这篇关于如何在SQL中对varchar字段进行排序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！