October 8, 2009 at 12:39 pm
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.
October 8, 2009 at 1:20 pm
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
October 9, 2009 at 7:18 am
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
October 9, 2009 at 8:05 am
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
May 24, 2012 at 12:30 pm
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.
May 24, 2012 at 6:54 pm
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 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
February 27, 2013 at 9:36 am
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
February 28, 2013 at 9:45 am
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 ;-))
October 29, 2013 at 11:22 am
"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.
October 29, 2013 at 12:27 pm
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/
September 15, 2014 at 4:20 am
It was a very helpful.
At one glance solution.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply