CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user757207
user757207

Reputation: 303

How to check upper case existence length in a string - Sql Query

How to check upper case existence length in a string using Sql Query?

For Eg:

1.KKart - from this string the result should be 2, because it has 2 upper case letters. 2.WPOaaa - from this string the result should be 3, because it has 3 upper case letters.

Thanks in advance

Upvotes: 4

Views: 3953

Answers (2)

TonyMoutaux
TonyMoutaux

Reputation: 357

How about something like this :

SELECT len(replace(my_string_field,'abcdefghijklmnopqrstuvwxyz','')) as 'UpperLen'
FROM my_table

The principle is simply to replace all lower case char by nothing and counts the remaining.

Upvotes: -1

Christian Specht
Christian Specht

Reputation: 36421

There is no built-in T-SQL function for that.
You can use a user-defined function like this one:

CREATE FUNCTION CountUpperCase
(
    @input nvarchar(50)
)
RETURNS int
AS
BEGIN

    declare @len int
    declare @i int
    declare @count int
    declare @ascii int

    set @len = len(@input)
    set @i = 1
    set @count = 0

    while @i <= @len
    begin

        set @ascii = ascii(substring(@input, @i, 1))

        if @ascii >= 65 and @ascii <= 90
        begin
            set @count = @count +1
        end

        set @i = @i + 1

    end

    return @count

END

Usage (with the examples from your question):

select dbo.CountUpperCase('KKart') returns 2.
select dbo.CountUpperCase('WPOaaa') returns 3.

Upvotes: 4

Related Questions