使用特殊字符进行字母数字排序 [英] Alphanumeric sorting with special char

问题描述

CBSFBE20151202000017_000_1.tif

CBSFBE20151202000017_000_10.tif

CBSFBE20151202000017_000_100.tif

CBSFBE20151202000017_000_101.tif

CBSFBE20151202000017_000_102.tif

CBSFBE20151202000017_000_103.tif

CBSFBE20151202000017_000_104.tif

CBSFBE20151202000017_000_105.tif

CBSFBE20151202000017_000_106.tif

CBSFBE20151202000017_000_107.tif

CBSFBE20151202000017_000_108.tif

CBSFBE20151202000017_000_109.tif



我尝试过什么：



我必须在sql server中对包含一些特殊字符的字母数字值进行排序，我已经尝试了几个order by子句但它没有给出所需的输出（它输出为1,10,100而不是101应该是1,2,3..100）

我尝试通过字母和数字同时按顺序排序，但它没有'工作。

CBSFBE20151202000017_000_1.tif
CBSFBE20151202000017_000_10.tif
CBSFBE20151202000017_000_100.tif
CBSFBE20151202000017_000_101.tif
CBSFBE20151202000017_000_102.tif
CBSFBE20151202000017_000_103.tif
CBSFBE20151202000017_000_104.tif
CBSFBE20151202000017_000_105.tif
CBSFBE20151202000017_000_106.tif
CBSFBE20151202000017_000_107.tif
CBSFBE20151202000017_000_108.tif
CBSFBE20151202000017_000_109.tif

What I have tried:

I have to sort an alphanumeric value containing some special characters in sql server, I have tried several order by clause but it is not giving the desired output( It is giving the output as 1,10,100 than 101 it should be 1,2,3..100 )
I have tried ordering by alphabets and number at same time by split but it didn't worked.

推荐答案

基本上，你是存储错误：您应该考虑将其更改为三个字段和计算列以重新生成全名。为什么？因为SQL不擅长字符串处理，你需要分解日期，将其中的一部分转换为整数，然后将其用作排序值 - 或者你永远不会得到正确排序。



或者，更改名称，使它们全部零填充：

Basically, you've stored it wrong: you should consider changing it to three fields and a computed column to "regenerate" the full name. Why? Because SQL is not good at string handling and you need to break up the date, convert part of it to an integer, and then use that as the sort value - or you will never get a "proper" sort.

Alternatively, change the names so they are all "zero padded":

CBSFBE20151202000017_000_0001.tif
CBSFBE20151202000017_000_0010.tif
CBSFBE20151202000017_000_0100.tif
CBSFBE20151202000017_000_0101.tif
CBSFBE20151202000017_000_0102.tif

但这通常会影响很多系统并且比它的价值更麻烦。



你可以做你想做的事，但是......你需要：

1）打破文件名以删除所有内容后的确定。' - 这是CHARINDEX和LEFT

2）在最后一个'_'字符后提取部分 - 这更复杂： SQL Server中是否有LastIndexOf？ - 堆栈溢出 [ ^ ]将向您展示如何。

3）将您剩下的位（基于字符串的数字）转换为整数，并将其用作ORDER BY子句。



复杂吗？是。讨厌？是。每次你想要这样做都必须这样做？是。

看看我的意思是你把它存储错了？

But that generally impacts a lot of systems and is more trouble than it's worth.

YOu can do what you want, but ... you need to:
1) Break out the file name to remove everything after teh '.' - that's CHARINDEX and LEFT
2) Extract the part after the last '_' character - that's more complicated: Is there a LastIndexOf in SQL Server? - Stack Overflow[^] will show you how.
3) Convert the bit you have left - a string based number - to an integer, and use that as your ORDER BY clause.

Complicated? Yes. Nasty? Yes. Have to do it every time you want to do this? Yes.
See what I mean about "you've stored it wrong"?

这篇关于使用特殊字符进行字母数字排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！