确定列值(SQL)中最后一个大写字母的索引?

2021-09-14 00:00:00 string uppercase sql-server

简短版本: 有没有一种方法可以根据该值中最后一个大写字母的索引(位置)轻松提取和 ORDER BY 数据库列中的值的子字符串, 仅使用 SQL?

长版本:我有一个带有用户名字段的表，用户名的约定是名字的首字母大写，然后是姓氏的首字母大写，然后是其余的姓氏.因此，按用户名字段排序是错误的".按用户名值的子字符串排序理论上可行，例如

SUBSTRING(username,2, LEN(username))

...除了在其他两个首字母之间有大写中间首字母的值.我很想知道仅使用 SQL(MS SQL Server)是否有一种相当直接/简单的方法:

  1. 测试 DB 值中字符的大小写(并返回一个布尔值)
  2. 确定字符串值中最后一个大写字符的索引

假设这甚至是遥不可及的，我认为人们必须遍历每个用户名的各个字母才能完成它，使其效率极低，但如果您有一个神奇的快捷方式，请随时分享.注意:这个问题纯粹是学术性的，因为我决定采用更简单的方法.我只是好奇这是否可能.

解决方案

  1. 测试 DB 值中字符的大小写(并返回一个布尔值)

SQL Server 没有布尔数据类型.bit 经常被用来代替它.

DECLARE @Char CHAR(1) = 'f'选择演员表(案例当@Char LIKE '[A-Z]' COLLATE Latin1_General_Bin那么 1其他 0以位结束)/* 返回 0 */

请注意，在上述语法中使用二进制排序规则而不是区分大小写的排序规则子句很重要.如果使用 CS collat​​e 子句，则需要将模式完整拼写为 '[ABCDEFGHIJKLMNOPQRSTUVWXYZ]' 以避免匹配小写字符.

<块引用>

  1. 确定字符串值中最后一个大写字符的索引

SELECT PATINDEX('%[A-Z]%' COLLATE Latin1_General_Bin, REVERSE('Your String'))/* 返回一个基于索引 (6) */SELECT PATINDEX('%[A-Z]%' COLLATE Latin1_General_Bin, REVERSE('无大写'))/* 如果测试字符串不包含 A-Z 范围内的任何字母，则返回 0 */

<块引用>

  1. 根据您可以使用的规则提取姓氏

SELECT RIGHT(Name,PATINDEX('%[A-Z]%' COLLATE Latin1_General_Bin ,REVERSE(Name)))从你的桌子

Short version: Is there a way to easily extract and ORDER BY a substring of values in a DB column based on the index (position) of the last upper case letter in that value, only using SQL?

Long version: I have a table with a username field, and the convention for usernames is the capitalized first initial of the first name, followed by the capitalized first initial of the last name, followed by the rest of the last name. As a result, ordering by the username field is 'wrong'. Ordering by a substring of the username value would theoretically work, e.g.

SUBSTRING(username,2, LEN(username))

...except that there are values with a capitalized middle initials between the other two initials. I am curious to know if, using only SQL (MS SQL Server,) there is a fairly straightforward / simple way to:

  1. Test the case of a character in a DB value (and return a boolean)
  2. Determine the index of the last upper case character in a string value

Assuming this is even remotely possible, I assume one would have to loop through the individual letters of each username to accomplish it, making it terribly inefficient, but if you have a magical shortcut, feel free to share. Note: This question is purely academic as I have since decided to go a much simpler way. I am just curious if this is even possible.

解决方案

  1. Test the case of a character in a DB value (and return a boolean)

SQL Server does not have a boolean datatype. bit is often used in its place.

DECLARE @Char CHAR(1) = 'f'

SELECT CAST(CASE
              WHEN @Char LIKE '[A-Z]' COLLATE Latin1_General_Bin
                THEN 1
              ELSE 0
            END AS BIT) 
 /* Returns 0 */

Note it is important to use a binary collation rather than a case sensitive collate clause with the above syntax. If using a CS collate clause the pattern would need to be spelled out in full as '[ABCDEFGHIJKLMNOPQRSTUVWXYZ]' to avoid matching lower case characters.

  1. Determine the index of the last upper case character in a string value

SELECT PATINDEX('%[A-Z]%' COLLATE Latin1_General_Bin, REVERSE('Your String'))
/* Returns one based index (6 ) */

SELECT PATINDEX('%[A-Z]%' COLLATE Latin1_General_Bin, REVERSE('no capitals'))
/* Returns 0 if the test string doesn't contain any letters in the range A-Z */

  1. To extract the surname according to those rules you can use

SELECT RIGHT(Name,PATINDEX('%[A-Z]%' COLLATE Latin1_General_Bin ,REVERSE(Name)))
FROM YourTable

相关文章