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

  • from corporate

    SSC Veteran

    Points: 279

    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.

  • Mark Marinovic

    SSCrazy

    Points: 2421

    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: 184296

    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 Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • Piotr.Rodak

    SSCrazy Eights

    Points: 9276

    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

    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: 86873

    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

    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

    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

    “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: 286324

    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

    It was a very helpful.

    At one glance solution.

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

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