SQL code to find upper case values

YoannaYoanna Posts: 1 New member
edited February 5, 2021 2:05AM in General Forum
Hi guys,

I am writing a select statement that looks through a column with guid strings and returns only those values that contain upper case symbols. The column contains many strings containing both upper and lower case symbols, but I want to extract only those with upper case. I am using MS SQL Management Studio. Your help will be appreciated!

Thanks.

Answers

  • predovicpredovic Posts: 1 New member
    edited March 30, 2021 2:27AM
    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
  • Jeff ModenJeff Moden Posts: 7 Bronze 1
    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 ModenJeff Moden Posts: 7 Bronze 1
    edited July 17, 2021 4:54PM
    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 ModenJeff Moden Posts: 7 Bronze 1
    edited July 17, 2021 5:01PM
    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.
  • Mann123Mann123 Posts: 1 New member
    Now you can download the complete solutions of class 11th Chemistry subject in free of cost. class 11 ncert solutions chemistry
  • deandows1992deandows1992 Los AngelesPosts: 1 New member
    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
Sign In or Register to comment.