find whether a upper case letter is there in a given string

Question

find whether a upper case letter is there in a given string

from corporate

SSC Veteran

Points: 279
More actions
October 8, 2009 at 12:39 pm

#156299

Hi all,
i want to know whether a upper case letter is there in a given string or not?
if i provide 'Sarat' it needs to give me a flag value like 1
if i provide 'sarat' then it should give 0
else
from a given field in a table i want to retrieve only those rows doesnt contain any upper case letter in any position.
please let me know if you have answer.

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Mark Marinovic SSCrazy Points: 2472 More actions · Answer 1

Here's a simple example that will render the result you want. Please note that this will only answer whether an A-Z character exists in the string (does not do anything for numbers or special characters in the string). There are other options such as using regexp within SQL Server for more customizable output.

DECLARE

@TestString VARCHAR(100)

SET @TestString = 'Sarat'

SELECT CASE WHEN BINARY_CHECKSUM(@TestString) = BINARY_CHECKSUM(LOWER(@TestString)) THEN 0 ELSE 1 END AS DoesContainUpperCase

GO

MJM

Jack Corbett SSC Guru Points: 184397 More actions · Answer 2

Cool solution Mark. Not one I would have thought of. I'd have done something like this:

DECLARE @string VARCHAR(10)

SET @string = 'Sarat'

SELECT

CASE

WHEN @string LIKE '%s%' COLLATE Latin1_General_CS_AI THEN 'Lower Case s found'

WHEN @string LIKE '%S%' COLLATE Latin1_General_CS_AI THEN 'Upper Case S found'

ELSE 'No S found'

END

but I like yours better.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 3

Actually, this is simpler I think:

DECLARE @string VARCHAR(10)

SET @string = 'Sarat'

SELECT

CASE

WHEN @string = lower(@string) COLLATE Latin1_General_CS_AI

THEN 'No upper case found' ELSE 'Upper Case found'

END

Regards

Piotr

...and your only reply is slàinte mhath

f7_cisco SSC Journeyman Points: 76 More actions · Answer 4

Just a quick clarification:

SQL may consider 'SARAT' , 'Sarat' or 'sarat' as equals; depending on the Collation;

which means that you should use "...BINARY_CHECKSUM(LOWER('SARAT')..." ;

if simply using "...LOWER('SARAT')...", then you should add "...COLLATE Latin1_General_CS_AI...".

"_CS_" stands for Case-Sensitive...; by defaut the Collation is "_CI_" which means Case In-sensitive;

Gr8 answers from all of you!

Cisco.

Dwain Camps SSC Guru Points: 86978 More actions · Answer 5

Another option:

DECLARE @t TABLE (Names VARCHAR(20))

INSERT INTO @t

SELECT 'sarat' UNION ALL SELECT 'Sarat' UNION ALL SELECT 'SaRaT' UNION ALL SELECT 'SARAT'

SELECT Names, CASE WHEN Names2=LOWER(Names2) THEN 1 ELSE 0 END

FROM @t CROSS APPLY (SELECT Names COLLATE Latin1_General_CS_AI) x (Names2)

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

po 35213 SSC Journeyman Points: 91 More actions · Answer 6

A simple regular expression can do the job fine. I had to delete all records from my words table which begun with a capital letter. Here is what I used:

SELECT *

FROM `words`

WHERE `text` REGEXP BINARY '^[A-Z]'

replacing "SELECT" with "DELETE" worked well.

Theodore Pokama

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 7

po 35213 (2/27/2013)
A simple regular expression can do the job fine. I had to delete all records from my words table which begun with a capital letter. Here is what I used:
SELECT *
FROM `words`
WHERE `text` REGEXP BINARY '^[A-Z]'
replacing "SELECT" with "DELETE" worked well.
Theodore Pokama

It's not MySql forum...

There are no REGEXP function in T-SQL (as yet ;-))

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

shornick Valued Member Points: 65 More actions · Answer 8

"from a given field in a table i want to retrieve only those rows doesnt contain any upper case letter in any position."

Why not just do something simple like:

select * from myTable

where myField = lower(myField)

That would return the rows that do not have any uppercase letters in the field in question.

Sean Lange SSC Guru Points: 286598 More actions · Answer 9

shornick (10/29/2013)
"from a given field in a table i want to retrieve only those rows doesnt contain any upper case letter in any position."
Why not just do something simple like:
select * from myTable
where myField = lower(myField)
That would return the rows that do not have any uppercase letters in the field in question.

That actually would depend on your collation. If you are on a case insensitive collation (default) that query ignores upper case. In other words THIS = this.

You would need to modify your query slightly like this.

select * from myTable COLLATE Latin1_General_CS_AI

where myField = lower(myField)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

guptashailesh.87 SSC Enthusiast Points: 177 More actions · Answer 10

guptashailesh.87

SSC Enthusiast

Points: 177

September 15, 2014 at 4:20 am

#1745423

It was a very helpful.

At one glance solution.

naumon765 SSC Rookie Points: 29 More actions · Answer 11

hi

hope this helps

DROP TABLE IF EXISTS SampleData;

CREATE TABLE SampleData

(

col1 NVARCHAR(max)

);

INSERT INTO SampleData (col1)

select 'Sarat'

union ALL

select 'qwerty'

union ALL

select 'AS123werTy'

t-sql solution

select

col1

,case when

col1 COLLATE Latin1_General_CS_AS <> lower(col1 COLLATE Latin1_General_CS_AS) then 1

else 0 end as result

from

SampleData

Steve Collins SSCrazy Points: 2265 More actions · Answer 12

This is an ancient topic. Doesn't matter to me tho. Looking through these answers I don't see one I like. The LOWER() solutions express the test indirectly. The _CS_ collations are being applied to the column but the range criteria may not mean “only ASCII uppercase A through Z”. The safer, simpler way imo would be to use a binary collation with the explicit range. The intention seems clear at a glance from something like this

select iif(patindex('%[A-Z]%', t.string COLLATE Latin1_General_BIN2)>0,1,0)
from (values ('Sarat'),
             ('sarat'),
             ('SARAt')) t(string);

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Steve Jones - SSC Editor SSC Guru Points: 742259 More actions · Answer 13

I like that, it's a nice clean solution. I ran this through Claude and it did what I was thinking, which was use col=lower() collate. That's assuming Latin characters, as you pointed out above.

Claude solution:

SELECT 
    col,
    CASE 
        WHEN col = LOWER(col) COLLATE Latin1_General_CS_AS 
        THEN 1 
        ELSE 0 
    END AS is_all_lower
FROM your_table;

I ran yours through it the AI liked it better:

2026-05_0140