Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

find whether a upper case letter is there in a given string Expand / Collapse
Author
Message
Posted Thursday, October 08, 2009 12:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 12, 2012 11:56 AM
Points: 7, Visits: 43
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.
Post #800271
Posted Thursday, October 08, 2009 1:20 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 04, 2014 1:56 PM
Points: 223, Visits: 330
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
Post #800298
Posted Friday, October 09, 2009 7:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:24 AM
Points: 10,907, Visits: 12,540
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #800716
Posted Friday, October 09, 2009 8:05 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 10, 2012 9:49 AM
Points: 800, Visits: 1,759
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
Post #800765
Posted Thursday, May 24, 2012 12:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 17, 2013 9:04 AM
Points: 2, Visits: 8
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.
Post #1306059
Posted Thursday, May 24, 2012 6:54 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:24 PM
Points: 3,589, Visits: 5,095
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1306262
Posted Wednesday, February 27, 2013 9:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 27, 2013 9:36 AM
Points: 1, Visits: 1
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
Post #1424606
Posted Thursday, February 28, 2013 9:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1425137
Posted Tuesday, October 29, 2013 11:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 26, 2013 1:59 PM
Points: 1, Visits: 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.
Post #1509490
Posted Tuesday, October 29, 2013 12:27 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:21 AM
Points: 11,935, Visits: 10,971
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1509519
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse