SQL code to find upper case values

Yoanna

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.

predovic

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 Moden

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

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

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.

Mann123

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

deandows1992

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

fffgggfee

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.

danielmeyers

Thank you all, the information was really helpful 

bindumand

hello good info thaks

bindumand

hello good info thaks

PeterCrouch

Good afternoon my dear friends. I think that you very much want to have all technique working. If you have a broken washing machine then use the site where you can find a master who quickly and accurately can repair your washing machine.

Jeff Moden

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.