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