問題描述
簡短版本: 有沒有一種方法可以根據該值中最后一個大寫字母的索引(位置)輕松提取和 ORDER BY 數據庫列中的值的子字符串, 僅使用 SQL?
長版本:我有一個帶有用戶名字段的表，用戶名的約定是名字的首字母大寫，然后是姓氏的首字母大寫，然后是其余的姓氏.因此，按用戶名字段排序是錯誤的".按用戶名值的子字符串排序理論上可行，例如
SUBSTRING(username,2, LEN(username))
...除了在其他兩個首字母之間有大寫中間首字母的值.我很想知道僅使用 SQL(MS SQL Server)是否有一種相當直接/簡單的方法:
- 測試 DB 值中字符的大小寫(并返回一個布爾值)
- 確定字符串值中最后一個大寫字符的索引
假設這甚至是遙不可及的，我認為人們必須遍歷每個用戶名的各個字母才能完成它，使其效率極低，但如果您有一個神奇的快捷方式，請隨時分享.注意:這個問題純粹是學術性的，因為我決定采用更簡單的方法.我只是好奇這是否可能.
- 測試 DB 值中字符的大小寫(并返回一個布爾值)
SQL Server 沒有布爾數據類型.bit 經常被用來代替它.
DECLARE @Char CHAR(1) = 'f'選擇演員表(案例當@Char LIKE '[A-Z]' COLLATE Latin1_General_Bin那么 1其他 0以位結束)/* 返回 0 */請注意，在上述語法中使用二進制排序規則而不是區分大小寫的排序規則子句很重要.如果使用 CS collat??e 子句，則需要將模式完整拼寫為 '[ABCDEFGHIJKLMNOPQRSTUVWXYZ]' 以避免匹配小寫字符.
- 確定字符串值中最后一個大寫字符的索引
SELECT PATINDEX('%[A-Z]%' COLLATE Latin1_General_Bin, REVERSE('Your String'))/* 返回一個基于索引 (6) */SELECT PATINDEX('%[A-Z]%' COLLATE Latin1_General_Bin, REVERSE('無大寫'))/* 如果測試字符串不包含 A-Z 范圍內的任何字母，則返回 0 */<塊引用>
- 根據您可以使用的規則提取姓氏
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:
- Test the case of a character in a DB value (and return a boolean)
- 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.
- 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.
- 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 */
- 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
這篇關于確定列值(SQL)中最后一個大寫字母的索引?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！