For example, the SQL Server Collations sort upper-case and lower-case letters in the opposite order as the Windows Collations.
Of course, what those extra characters would be is on a per-Code Page basis.Īlso, please be aware that both Collation type (SQL Server vs Windows) and sensitivity settings (case, accent, etc sensitive vs insensitive) will affect which characters are included in a particular range. Þ = Latin capital "Thorn" = SELECT CHAR(0xDE)), then you might need to include those in the character class.
Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at all posts by Rajendra GuptaIf you are guaranteed to only ever use the 26 letters of the US English alphabet (both upper-case and lower-case versions) then sure, you can get away with using LIKE and/or PATINDEX with the simple range notation of (you wouldn't need to use an upper-case "Z" when using a case-insensitive Collation).īut, if you might get characters not found in the en-US alphabet yet available in various Code Pages / Collations for VARCHAR data (e.g. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 20 at SQLShack.
He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. Execute this query in SSMS and view output in Result to text (short cut key CTRL + T) format:Īs an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. We use a comma to separate the name of the month. Suppose we have a string that contains a month’s name. Let’s explore these ASCII codes with CHAR functions with examples. We can use the following ASCII codes in SQL Server: In SQL Server, we can use the CHAR function with ASCII number code. We might require inserting a carriage return or line break while working with the string data. Insert SQL carriage return and line feed in a string With the disabled option of retain CR/LF on copy or save: With the enabled option of retain CR/LF on copy or save: It shows that while copying output to notepad or excel sheet, SQL Server does not retain SQL carriage return on copy/save: In SSMS 2016 and higher, we can see that “Retain CR/LF on copy or save” checkbox is not ticked. Navigate to Tools | Options | Query Results | SQL Server | Results to Grid. SSMS allows us to define the carriage return behavior as well. It does return to the beginning of the line
SSMS 2016 and higher removes carriage return. We might get different behavior of the carriage return with different versions of SSMS. It retains the carriage return as well while copying the output in notepad or excel: You can see that in both notepad and excel sheet row one and two splits in multiple lines. Let’s copy the output in Excel and Notepad. In the SSMS output, we see all text in a single line: