SQL code to find upper case values

• 
  you should use the SUBSTRING function:  

            SUBSTRING (string, start, length)

  • string: can be a string, variable or column that you want to extract.
  • start: is an integer specifying the position where the substring should be returned from. Note that the first character in the string is 1, not 0.
  • length: a positive integer specifying the number of characters of the substring to be returned from the string.
    Note:
  • If the length parameter is negative then SUBSTRING will error
    
  • If start + length> the length of a string, then the substring will start at start and include the rest of the string.
  • See more about the LEFT and RIGHT functions as well to extract a substring from a specified string.
  • The SUBSTRING function can be used in the following versions of SQL Server: SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005.
    jigsaw puzzle

  predovic March 24, 2021 02:59

  - 0 +
• 
  From the original post:

  "I am writing a select statement that looks through a column with guid strings..."

  
  

  Why anyone would convert 16 byte GUIDS to 36 characters (72 bytes if Unicode!!!) is beyond me.  I've heard a lot of "reasons" people offer but none of them have been good.

  
  

  And now we need to find the GUIDs that might have upper case letters in them (which IS the default for how SQL Server displays UNIQUEIDENTIFIERS, btw).  I cannot imagine a good reason to do this.  And, no... I'm not blaming the OP for any of this. 
  

  
  

  Anyway... getting to the problem.  I might be reading this incorrectly but returning the "values" in this context seems to mean returning the entire value from the column if it contains any upper case letters.  Following only that rule, the code is quite simple once you realize that you can easily make the comparison case sensitive by using a binary collation, which is also nasty fast.

  
  

  The thing you're looking for is in the WHERE clause of the following example code.

  
  

  <br>&nbsp;SELECT *<br>&nbsp;&nbsp; FROM (VALUES --This represents the values in a table<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (1,'02830916-85AB-47FF-A2CA-2228441840EE','All UPPER CASE')<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,(2,'02830916-85ab-47ff-a2ca-2228441840ee','All LOWER CASE')<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,(3,'02830916-85aB-47ff-a2ca-2228441840ee','One UPPER CASE')<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )v(RowNum, GUIDString, Explanation)<br>&nbsp; WHERE GUIDString LIKE '%[A-F]%' COLLATE Latin1_General_BIN<br>;

  
  

  
  

  
  

  
  

  Jeff Moden July 17, 2021 16:42

  - 0 +
• 
  There's an easy way to do this by using Collation to make things Case Sensitive.  What' you're looking for is what is in the WHERE clause in the code below.

  
  

   SELECT *
     FROM (VALUES --This represents the values in a table
           (1,'02830916-85AB-47FF-A2CA-2228441840EE','All UPPER CASE')
          ,(2,'02830916-85ab-47ff-a2ca-2228441840ee','All LOWER CASE')
          ,(3,'02830916-85aB-47ff-a2ca-2228441840ee','One UPPER CASE')
          )v(RowNum, GUIDString, Explanation)
    WHERE GUIDString LIKE '%[A-F]%' COLLATE Latin1_General_BIN
  ;
  

  
  

  As a bit of a sidebar, whoever designed the column as a string datatype instead of the much more appropriate UNIQUEIDENTIFIER, did you a HUGE disservice.

  
  

  Please pardon the formatting of the code.  For some reason, the "CODE" paragraph type doesn't like my code and so I had to post it as plain text.

  
  

  Heh... Imagine that.. a forum design to support SQL that doesn't. 
  

  
  
  
  
  
  

  Jeff Moden July 17, 2021 16:50

  - 0 +
• 
  Whomever saved GUIDs in a column with a string datatype did you a HUGE dis-service.  If I'm reading your problem correctly, the WHERE clause in the following demo code is what you're looking for.  It works by using collation to make the comparison CASE SENSITIVE.

   SELECT *
     FROM (VALUES --This represents the values in a table
           (1,'02830916-85AB-47FF-A2CA-2228441840EE','All UPPER CASE')
          ,(2,'02830916-85ab-47ff-a2ca-2228441840ee','All LOWER CASE')
          ,(3,'02830916-85aB-47ff-a2ca-2228441840ee','One UPPER CASE')
          )v(RowNum, GUIDString, Explanation)
    WHERE GUIDString LIKE '%[A-F]%' COLLATE Latin1_General_BIN
  ;
  

  And, sorry about the lack of formatting in the code.  The "CODE" paragraph type in this forum doesn't work correctly and kept throwing errors or showing the actual html for the code above.

  Imagine that... an SQL Forum that doesn't support SQL Posts.
  

  Jeff Moden July 17, 2021 16:58

  - 0 +
• 
  Now you can download the complete solutions of class 11th Chemistry subject in free of cost. class 11 ncert solutions chemistry

  Mann123 August 08, 2021 04:08

  - 0 +
• 
  in such difficult situations, I seek help from specialists from https://grademiners.com/custom-essay who write me an instruction or a very understandable article on this material. have never failed. I also prepare professional resumes and term papers
  

  deandows1992 November 18, 2021 10:52

  - 0 +
• 
  I can tell you for sure that there is only one excellent service that writes well, and if you want to find it, then you should definitely visit this site Rushmyessay . Here you will be able to find quite a good option, which allows you Not to worry about whether you will write your essay or not, because for you it will be done by real professionals, who always write essays to the highest score.
  

  fffgggfee July 31, 2022 09:41

  - 0 +
• 
  Thank you all, the information was really helpful

  danielmeyers October 07, 2022 10:34

  - 0 +
• 
  hello good info thaks

  bindumand January 20, 2023 19:09

  - 0 +
• 
  hello good info thaks

  bindumand January 20, 2023 19:10

  - 0 +
• 
  Lordy... look at all the stupid spam this attracted.  You folks at Redgate need to get a better rake.  You folks posting all the spam need to get both a life and a grip on how to effectively advertise.  I won't click on spam listings and almost everyone I know of won't either.  And, no... you're not clever at all with your "Good Info" posts although it does help me decide which sites visit or products/services to buy because if you'll use deception in spam, you'll use deception in your products and services.
  

  Jeff Moden March 13, 2023 15:03

  - 0 +
• 
  Great infor.

  KennethSNelson March 28, 2023 07:02

  - 0 +
• 
  As an option, use the help of an outsourcing company
  

  Shridhara May 02, 2023 16:43

  - 0 +
• 
  Hello,

  
  

  To filter out only the GUIDs containing upper case symbols in MS SQL Management Studio, you can use the COLLATE clause with a case-sensitive collation.
  Here is an example : 
  SELECT YourGUIDColumn

  FROM YourTableName

  WHERE YourGUIDColumn COLLATE Latin1_General_CS_AS LIKE '%[A-Z]%';
  
  

  Replace YourGUIDColumn with the actual column name and YourTableName with the table name. The Latin1_General_CS_AS collation makes the comparison case-sensitive, and the LIKE '%[A-Z]%' condition ensures that only values with at least one upper case letter are selected.

  
  

  I hope this helps!
  

  
  

  
  

  rohan02 November 07, 2023 05:25

  - 0 +
• 

  To find uppercase values in a SQL column, you can use the UPPER function along with a comparison. Here's an example:

  Assuming you have a table named your_table and you want to find uppercase values in the your_column column:

  sqlCopy codeSELECT your_column
  FROM your_table
  WHERE your_column = UPPER(your_column);
  

  In this query:

  • UPPER(your_column) converts all values in your_column to uppercase.
  • The WHERE clause filters rows where the original value is equal to its uppercase version, meaning it was already in uppercase.

  Keep in mind that this comparison will only find exact matches between the original and uppercase versions of the values. If you want to find rows where any uppercase letter exists, you might need to use a different approach depending on your SQL database system.

  For example, in SQL Server, you can use the COLLATE clause with a case-insensitive collation:

  sqlCopy codeSELECT your_column
  FROM your_table
  WHERE your_column COLLATE SQL_Latin1_General_CP1_CS_AS = UPPER(your_column);
  

  This query performs a case-sensitive comparison, and if the original and uppercase values match, it implies that the original value is already in uppercase. Adjust the collation according to your specific SQL database system and collation settings.

  Sergio1 November 10, 2023 13:09

  - 0 +
• 
  AllEssayWriter.com offers a reliable and professional custom essays writing services that can help enhance your academic performance and alleviate the stress of writing tasks.
  

  declan2001 July 30, 2024 08:58

  - 0 +
• 
  Interesting

  oliviamaya August 07, 2024 07:51

  - 0 +

Add comment

Please sign in to leave a comment.