What are the challenges you face when working across database platforms? Take the survey
Options

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

  • Options
    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
  • Options
    Jeff ModenJeff Moden Posts: 8 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>;




  • Options
    Jeff ModenJeff Moden Posts: 8 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. 





  • Options
    Jeff ModenJeff Moden Posts: 8 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.
  • Options
    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
  • Options
    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
  • Options
    fffgggfeefffgggfee Posts: 2 New member
    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.
    If you're on the hunt for a new online casino to test your luck, I can't recommend https://slotscity.com/game-hall enough. The site boasts a remarkable selection of games, ensuring there's something for everyone. Plus, the user interface is incredibly intuitive, making for a seamless gaming experience.
  • Options
    Thank you all, the information was really helpful 
    During my studies, one of the most difficult tasks for me was writing, so writing my paper at https://writemypapers4me.net/ was a task for other people
  • Options
    bindumandbindumand Posts: 2 New member
    hello good info thaks
  • Options
    bindumandbindumand Posts: 2 New member
    hello good info thaks
  • Options
    Jeff ModenJeff Moden Posts: 8 Bronze 1
    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.
  • Options
    KennethSNelsonKennethSNelson Posts: 2 New member
    edited March 28, 2023 7:03AM
    Great infor. 
  • Options
    ShridharaShridhara Posts: 1 New member
    As an option, use the help of an outsourcing company
  • Options
    rohan02rohan02 Posts: 2 New member
    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!


  • Options
    Sergio1Sergio1 Posts: 11 New member

    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.

Sign In or Register to comment.