SQL对字符进行排序(数字类的字符)

最新推荐文章于 2025-05-08 21:37:31 发布
最新推荐文章于 2025-05-08 21:37:31 发布
阅读量1.4w 收藏 2
点赞数 3
CC 4.0 BY-SA版权
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/qq_28204865/article/details/81532677

本文使用的是SQLserver数据库测试,其他数据库类似.

1.首先看下表结构,建表语法自行百度.

2.插入数据

默认排序是按ID进行排序的

select stu_id ,stu_name from student order by stu_id.

效果如上图,现在需要按姓名排序,姓名数据类型是字符,但里面的内容数字.

下来测试按姓名排序:

select stu_id ,stu_name from student order by stu_name.

效果如下图:

观察发现并不是按预想的格式从小到大排序,而是首位数字最小的在前面.

有人会说直接将name这个字段转换成数字,会发现是不行的.13行14行又不是纯数字,无法进行强制转换

博主这来测试了一个写法大家可以参考下.

select stu_id ,stu_name from student order by  right ('0000000000'+stu_name,10)

具体为何这样写,大家可以自行百度,实际效果如下图:

SQL学习笔记-数据排序
weixin_42264784的博客
05-23 815
1.order by 语句升序和降序时的属性需要是数值型，char型是不会排序的，升序是ASC,降序是DESC2.order by语句需要是select中的最后一条语句。3.order by语句不加ASC和DESC时，默认升序。4.可以使用列别名进行排序。5.因为比较运算符不能操作null，所以对null值不进行排序。
MySQL关于字符串中数字排序的问题分析
09-10
在MySQL中，处理字符串中的数字排序可能会遇到一些特殊的情况，因为MySQL默认会按照字符串的字典顺序进行排序，而不是按照数字的大小。当字段的定义是字符串类型（如VARCHAR），但实际存储的是数字时，如果不做特殊...
SQL2005下字符串字段内的字符排序
Happyflystone　－　无枪狙击手
01-17 3269
SQL2005下字符串字段内的字符排序      今天和梁翁在群里聊天，小家伙突然抛出一个有意思的问题，那就是字符串字段内的字符串排序问题，比如有列col,有数据RDGS ,要求输出为DGRS。 当时我本想和梁翁讨论一种思路，那山羊闹着要聊天，不许讨论技术问题，所以这夜深时俺整理一下，与大家分享。   ----------------------
SQL：MySQL函数：字符串函数
最新发布
2402_88047672的博客
05-08 1031
我们用数据库存储信息，经常会处理文字/文本（如：用户名、推文、商品名、评论内容）。但是这些文字往往：太长、太短（需要裁剪）多余空格（要去掉）出现错别字/旧字段（要替换）需要统计长度（做验证、展示控制）👉 所以，就像洗衣机、剪刀、胶水，是处理文本的“工具箱”。
sql对字符串进行排序
热门推荐
slm311的专栏
09-07 1万+
tp5写法： $gold = Db::table('table')->order('CAST(需要排序的字段 AS UNSIGNED) desc')->limit(10)->select(); 原生写法： select * from table  ORDER BY CAST(需要排序的字段 AS UNSIGNED) desc...
换个思路"SQL2005下字符串字段内的字符排序"
jinjazz
01-20 2690
这个是狙狙的sql解法。http://blog.youkuaiyun.com/happyflystone/archive/2009/01/17/3819863.aspx 引用需求今天和梁翁在群里聊天，小家伙突然抛出一个有意思的问题，那就是字符串字段内的字符串排序问题，比如有列col,有数据RDGS ,要求输出为DGRS。  依靠ascii来分解字符串的时候碰到相
sql对字符串类型的数字排序
weixin_40759747的博客
08-15 572
近段在处理sql时候，遇到一个问题，数据表有一个字段是字符串类型，但是保存的是数字类型，要对这个字段进行排序一、问题发现在默认情况下使用order by 字段名称 desc/asc 进行排序的时候，是根据字典顺序进行排序的，那就有问题了，字符1与字符11这种不会按照大小进行排序在SQL中，对字符串数字进行排序时，通常需要将...
JAVA像SQL一样对List对象集合进行排序
08-18
JAVA像SQL一样对List对象集合进行排序 在本文中，我们将详细介绍如何使用JAVA像SQL一样对List对象集合进行排序。我们将通过五个部分，分别介绍字符串List排序、整数类型List排序、对象字段对对象List排序、使用...
使用sql查询从字符串中删除或替换多个特殊字符。
04-07
在SQL Server中，处理字符串是常见的任务之一，尤其是在数据清洗和预处理阶段。当你需要从字段中删除或替换多个特殊字符时，可以借助SQL的内置函数和正则表达式（虽然SQL Server本身不直接支持正则表达式，但可以...
解决sql server保存对象字符串转换成uniqueidentifier失败的问题
12-14
GUID是一个128位的整数，通常以`xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx`的形式表示，这里的每个`x`都是一个0到9或a到f的十六进制数字。这种数据类型常用于需要唯一标识记录的场景，如主键。 在给定的问题中，Web...
SQL 字符串排序 （字符串为数字、字母、汉字）
神戳戳的神
06-08 4625
排序前的结果 1, 11, 12, 2, 21, 22 --sql 处理 使用isnumeric() 函数 和 numeric() 函数 select * from work_order order by case when isnumeric(OR_No_Txt)=1 then 0 else 1 end ,  case when isnumeric(OR_No_Txt)
SQL 数字字符串 排序
老张的专栏
06-11 852
     //order   by   convert(int,substring(字段名,2,len(字段名)))            string strSql = "select top 1" + fieldlist[0] + " from  " + STABLENAME + " order   by  convert(int," + fieldlist[0] + ") desc";
sql字符串排序
flj924的专栏
04-19 2125
有一个字段内容是M0,M1,M2,M3等等 排序如下： SELECT * from detail p WHERE p.type = 3 ORDER BY substr(p.age, 1) asc; SELECT * from detail p WHERE p.type = 3 ORDER BY replace( p.age, 'M', '0') asc; SELECT * from detai...
sql中对长度不一致的包含数字的字符串排序
chedanquestion的博客
10-26 490
幸好找到了解决办法，利用sql的正则表达式把非数字统统替换为空，然后把字符串转换为数字即可。这样的话文件号就变成了2023999、2023998、20231000这种形式，既有了年份，又有了后面的数字，排序效果杠杠的。遇到的问题主要是文件有文件号，形式是[2023]999、M[2023]998、M[2023]1000这种形式的数据，而且必须按照文件号排序，当初没有把前缀和后缀分开，想要排序很麻烦。这个项目用的是pgsql，排序sql如下，其他的数据库可以搜一下相应的函数。
linqto 多个关键字模糊查询_搞不懂这些查询语句，SQL简单查询也无法从入门到熟练...
weixin_39816141的博客
12-03 230
大纲：一、基本的查询语句二、指定查询条件三、注释和SQL语句注意事项四、运算符五、谓词一、基本的查询语句从表中选取数据时需要使用select语句，也就是只从表中选出（select）必要数据的意思。通过select语句查询并选取出必要数据的过程，称为匹配查询或查询（query）。1.如何查找出所有数据？星号（*）：查询出全部列select 2.如何为列设定别名？as：为列设定别名select 选中s...
sql语句字符串型数字排序
ZT1090258642的博客
05-26 2169
转载链接：https://blog.youkuaiyun.com/weixin_41402352/article/details/82288831 order by 字段名称+0 desc/asc的形式进行排序 order by 字段名称*1 desc/asc的形式进行排序
带有字符的数字排序 sql_简单的sql语句
weixin_39611863的博客
12-09 839
一、基本查询语句注意点：1、为列名设置别名时，如果是中文别名，需要用英文单引号括起来2、删除重复数据distinct，如果对多个列使用，仅在所选列数据全部相同时才算作重复数据3、对列名命名时不能有空格二、指定查询条件注意点：1、运行顺序：select语句最后运行。三、注释和sql语句注意事项注意事项：1、子句中间不要空行2、不要用中文符号四、运算符（一）算数运算符与比较运算符注意点：1、运算符有括...
关于SQL对数值类型的字符串进行排序问题
weixin_37926421的博客
02-13 1060
一、博客背景 今天在做毕设，需要排序，但是结果令我大跌眼镜。 这四不像的排序是什么玩意 二、问题来源及解决方案 问题来源是这样的，首先我的house_size的类型不是数值型，是字符串型。 那么排序的时候它就是这样的，比如：1开头不管多少位绝对不可以大于2开头的，同一个开头的就看第二个，同样的原理，上边例子就是110不可能大于30、31、35，但30跟31都是同一个开头，那么看第二位，0小于1...
第十章 SQL排序（一）
yaoxin521123的博客
03-15 3178
文章目录 第十章 SQL排序排序类型命名空间范围的默认排序规则表字段/属性定义排序索引定义排序 第十章 SQL排序 排序规则指定值的排序和比较方式，并且是InterSystems SQL和InterSystemsIRIS®数据平台对象的一部分。有两种基本排序规则：数字和字符串。 数值排序规则按以下顺序基于完整数字对数字进行排序：null，然后是负数，从最大到最小，零，然后是正数，从最小到最大。这将创建如下序列：–210，–185，–54，–34，-.02、0、1、2、10、17、100、120。 字符串
mysql 对字符串包含的数字排序
04-28
<think>嗯，用户想在MySQL中对包含数字的字符串进行排序。这个问题看起来有点挑战性，因为字符串排序通常是按字符逐个比较的，比如"item10"会被排在"item2"前面，但用户可能希望按数字部分的数值大小来排序。那该怎么办呢？ 首先，我需要回忆一下MySQL的字符串处理函数。有没有办法把字符串中的数字提取出来，然后转换成数值进行排序？比如使用正则表达式函数REGEXP_SUBSTR，这可能是一个办法。不过MySQL的版本是否支持这个函数呢？记得在MySQL 8.0及以上版本才有这些正则表达式函数。如果用户用的是旧版本，可能需要其他方法。 比如，假设有一个字段是"item2", "item10"，按常规排序的话，"item10"会出现在"item2"之前，因为字符'1'比'2'小。但如果能提取出数字部分，转换成整数再排序，就能正确得到2在10前面的顺序了。 那具体怎么操作呢？可能需要用SUBSTRING和REGEXP来提取数字部分。例如，使用ORDER BY CAST(SUBSTRING(column_name FROM REGEXP '[0-9]+') AS UNSIGNED)。不过需要注意，如果字符串中有多个数字部分，REGEXP可能会匹配到第一个出现的数字，或者最后一个？需要测试一下。 另外，用户的数据可能格式不一致，有些可能没有数字，这时候提取出来的可能是空值，会导致转换失败。这时候可能需要用COALESCE或者IFNULL处理一下，或者使用CASE语句来判断是否有数字存在。 还有一个方法是在排序时使用自然排序，比如在ORDER BY子句中使用column_name+0，这会隐式转换为数字，但可能只适用于以数字开头的字符串，比如"100abc"会被转换为100，而"abc100"可能转换为0。这可能不符合用户的需求，因为如果字符串开头不是数字，转换结果可能不正确。 此外，用户可能需要同时考虑字符串中的非数字部分和数字部分。比如，"item2a"和"item2b"应该按字母顺序排序，而数字部分相同的话，再比较后面的字符。这时候可能需要更复杂的处理，比如将字符串拆分为多个部分，分别比较数字和非数字段。 例如，可以考虑使用PATINDEX或者SUBSTRING_INDEX函数来分割字符串，但MySQL中可能没有PATINDEX，需要用其他方法。或者，使用自定义的函数来循环提取每个数字和非数字部分，但这可能在查询中效率不高，尤其是处理大量数据时。 回到问题本身，用户可能只需要处理字符串中的第一个数字部分，或者最后一个数字部分。比如，产品编号可能是"ABC123-456"，需要提取中间的某个数字部分排序。这时候需要明确用户的字符串结构，但用户的问题中没有给出具体例子，可能需要假设最常见的场景。 所以，总结可能的解决方案： 1. 提取字符串中的数字部分并转换为整数排序，适用于数字在字符串中的位置固定的情况。 2. 使用自然排序技巧，如column_name+0，适用于数字在开头的情况。 3. 分割字符串为多个部分，分别处理数字和非数字段，适用于复杂结构的情况。 4. 对于MySQL 8.0+，使用REGEXP_SUBSTR提取数字，然后转换排序。 5. 对于旧版本，可能需要使用SUBSTRING和LOCATE函数手动提取数字。 另外，性能方面，这些方法可能会影响查询效率，尤其是当数据量大的时候，因为无法使用索引。这时候可能需要添加生成列，存储提取后的数字，并建立索引，以优化排序性能。 比如，可以添加一个虚拟列，存储提取的数字，然后对该列进行排序，并建立索引。但需要注意虚拟列的计算方式是否确定，是否可以被索引。 例如： ALTER TABLE products ADD COLUMN numeric_part INT AS (CAST(SUBSTRING(name FROM REGEXP '[0-9]+') AS UNSIGNED)) STORED; CREATE INDEX idx_numeric_part ON products(numeric_part); 这样在排序时就可以直接使用ORDER BY numeric_part，提高效率。 但用户可能需要考虑存储空间和更新成本，因为STORED列会占用存储空间，并且在插入或更新时需要计算。 此外，如果字符串中的数字部分可能有前导零，比如"item0012"，转换为整数时会变成12，这时候排序可能正确，但显示的时候可能需要保留前导零，这可能需要另外处理。 还有，如果字符串中有多个数字，比如"item3-4"，这时候提取的是3还是4？需要根据具体情况调整正则表达式，可能需要匹配最后一个数字，或者最大的连续数字部分。 例如，使用REGEXP '[0-9]+'默认会匹配第一个出现的数字，如果用户需要最后一个，可能需要调整正则表达式，例如'[0-9]+([^0-9]*)$'，然后捕获最后一个数字部分，但MySQL的正则表达式函数是否支持捕获组？可能需要测试。 例如，REGEXP_SUBSTR(column, '[0-9]+', 1, 1)可以提取第一个出现的数字，而REGEXP_SUBSTR(column, '[0-9]+', 1, 2)提取第二个出现的数字，依此类推。这可能适用于有多个数字段的情况，但需要用户明确他们的需求。 比如，如果字符串是"version2.5.10"，可能需要提取主版本号2，次版本号5，修订号10，分别进行排序，这时候可能需要多个提取步骤。 但用户的问题比较笼统，可能需要给出通用的方法，并提醒用户根据具体情况调整。 再参考用户提供的引用，其中引用[2]提到在比较不同列的值时，最好使用相同的字符集和排序规则，以避免查询时的字符串转换。这可能和当前问题无关，但需要确认用户的问题是否涉及到字符集问题，比如字符串中的数字是否受到排序规则的影响。但一般来说，排序规则主要影响的是字符的顺序，而不是数字的提取。 引用[3]提到MySQL在排序时会使用排序缓冲区和临时文件，这可能对处理大数据量时的性能有影响。因此，在优化排序时，可以考虑使用索引或者生成列来减少排序时的开销。 引用[4]和[5]是关于Nacos和Docker的配置，可能和当前问题无关，可以忽略。 回到解决方案，可能的步骤： 1. 确定字符串中数字的位置和格式。 2. 使用正则表达式或字符串函数提取数字。 3. 转换为整数进行排序。 4. 考虑性能和索引优化。 例如，示例数据： 表products中的name字段： - "item1" - "item10" - "item2" - "product5" - "product20" 常规排序会得到"item1", "item10", "item2"，因为按字符顺序。而用户期望的是按数字部分排序，得到"item1", "item2", "item10", "product5", "product20"。 使用以下查询： SELECT name FROM products ORDER BY CAST(REGEXP_SUBSTR(name, '[0-9]+') AS UNSIGNED), name; 这会先按提取的数字排序，如果数字相同，再按整个字符串排序。但需要确认REGEXP_SUBSTR在用户MySQL版本中的可用性。 如果用户使用的是MySQL 8.0以下版本，可能需要使用SUBSTRING和LOCATE来手动提取数字。例如： SUBSTRING(name FROM PATINDEX('%[0-9]%', name))，但MySQL中没有PATINDEX，可能需要使用LOCATE和正则表达式。 或者，使用SUBSTRING(name FROM (LENGTH(SUBSTRING_INDEX(name, ' ', 1)) + 1))之类的组合函数，但需要具体分析字符串结构。 比如，假设数字部分在字符串末尾，可以用SUBSTRING_INDEX(name, '-', -1)来提取最后一个分隔符后的部分。 如果字符串结构不固定，可能需要更复杂的处理，甚至用户自定义函数。 总结回答的步骤： 1. 使用正则表达式提取数字部分。 2. 转换为整数进行排序。 3. 处理无数字的情况。 4. 优化性能的方法，如生成列和索引。 可能还需要注意，如果字符串中有多个数字，如何处理。例如，"item2-3"中的数字是2还是3？可能需要提取最大的数字，或者第一个出现的数字。这需要用户根据业务需求决定。 另外，测试查询的例子，用户可能需要实际运行来验证效果，比如： SELECT name, CAST(REGEXP_SUBSTR(name, '[0-9]+') AS UNSIGNED) AS num FROM products ORDER BY num, name; 这会将没有数字的行的num设为0，可能导致它们排在前面。如果需要将这些行排在后面，可能需要调整ORDER BY子句，例如： ORDER BY num = 0, num, name; 这样，num为0的行会被排在后面，非零的按数值排序，再按名称。 最后，提醒用户如果性能有问题，可以考虑生成列和索引，并注意版本兼容性。</think>在MySQL中对包含数字的字符串进行排序时，常规的字符串排序会按字符逐位比较（如`"item10"`排在`"item2"`前），但可通过以下方法实现按数值大小排序： --- ### 方法1：提取数字部分并转换为整数 使用正则表达式函数`REGEXP_SUBSTR`提取字符串中的数字部分，转换为整数后排序（适用于MySQL 8.0+）： ```sql SELECT column_name FROM table_name ORDER BY CAST(REGEXP_SUBSTR(column_name, '[0-9]+') AS UNSIGNED), column_name; ``` - **原理**：`REGEXP_SUBSTR(column_name, '[0-9]+')`会提取第一个连续数字片段，`CAST(... AS UNSIGNED)`将其转为整数[^3]。 - **示例**：字符串`"item12"`提取数字`12`，`"product5"`提取`5`，按数值排序后顺序正确。 --- ### 方法2：自然排序技巧 若数字在字符串开头，可通过隐式类型转换简化操作： ```sql SELECT column_name FROM table_name ORDER BY column_name + 0, column_name; ``` - **注意**：此方法仅适用于以数字开头的字符串（如`"100abc"`），若字符串不以数字开头，转换结果为`0`[^2]。 --- ### 方法3：处理复杂格式（多段数字） 若字符串含多个数字段（如`"ver2.5.10"`），需分段提取： ```sql SELECT column_name, CAST(REGEXP_SUBSTR(column_name, '[0-9]+', 1, 1) AS UNSIGNED) AS part1, CAST(REGEXP_SUBSTR(column_name, '[0-9]+', 1, 2) AS UNSIGNED) AS part2 FROM table_name ORDER BY part1, part2; ``` --- ### 性能优化建议 1. **生成列+索引** 若频繁排序，可添加存储计算结果的生成列并创建索引： ```sql ALTER TABLE table_name ADD COLUMN numeric_part INT AS (CAST(REGEXP_SUBSTR(column_name, '[0-9]+') AS UNSIGNED)) STORED; CREATE INDEX idx_numeric_part ON table_name(numeric_part); ``` - **优势**：避免每次查询时重复计算，提升排序效率[^3]。 2. **统一字符集** 确保比较的列使用相同字符集和排序规则，避免隐式转换影响性能[^2]。 --- ### 示例场景 假设表`products`含字段`name`： | name | |------------| | "item1" | | "item10" | | "item2" | | "product5" | 执行以下查询： ```sql SELECT name FROM products ORDER BY CAST(REGEXP_SUBSTR(name, '[0-9]+') AS UNSIGNED), name; ``` **结果**： ``` item1 item2 item10 product5 ``` ---
评论 3
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值