udf - find upper case
-
Hi,
I need some help with writing a udf which returns a 1 or 0 depending on if the field contains a letter in uppercase.
I would like to do this in a udf. like:
dbo.find_uppercase(fieldname).
alternatively, it could count the number of uppercase letters it finds.
Any help would be much appreciated,
Thanks,
David
-
Hi,
I thought this would count the number of uppercase letters in a field but counts lowercase letters too. Any suggestions?
create function dbo.find_uppercase1
(@name as varchar(255))
Returns varchar(255)
as
BEGIN
declare @current varchar(1)
declare @Last varchar(1)
declare @string varchar(255)
declare @length int
declare @counter int
declare @result int
set @string = ''
set @counter = 1
set @length = len(ltrim(rtrim(@name)))
set @Last = ''
set @current = left(ltrim(@name),1)
set @result = 0
while @counter <= @length
BEGIN
if not (@current = ' ' and @Last = ' ')
BEGIN
if @Last like '[A-Z]'
--set @result = @result + 1
if @Last not like '[A-Z]'
set @result = @result
BEGIN
if @Last = char(39) and @current = 's' and substring(ltrim(rtrim(@name)), @counter + 1,1) not like '[A-Z]'
set @result = @result
if @Last <> char(39) or @current <> 's' or substring(ltrim(rtrim(@name)), @counter + 1,1) like '[A-Z]'
set @result = @result + 1
END
END
set @counter = @counter + 1
set @Last = @current
set @current = substring(ltrim(rtrim(@name)), @counter,1)
END
set @result = @result
RETURN @result
END
-
I'll make one for you Monday (can't test anything on this pc).
Do you need to know how many there are, or just find if there are upper case letters. Also do you need to get this info in the same function?
-
Great. Thanks.
Just find if there are upper case letters i.e. return a 1(true) or (0)false would be ideal - for all entries within the field. I could then adapt it at a later date.
-
david - a function like this would count the # of uppercase instances and return you the number...you could modify it suit your needs:
<pre class="code"
CREATE FUNCTION dbo.CountUpperCase(
@String varchar(20))
RETURNS tinyint -- # of upper case instances
AS
BEGIN
DECLARE @Ctrtinyint
DECLARE @UcaseCtr tinyint
SET @Ctr = 1
SET @UcaseCtr = 0
WHILE (@Ctr <= LEN(@String))
BEGIN
IF (ASCII(SUBSTRING(@String, @Ctr, 1)) BETWEEN 65 AND 90)
BEGIN
SET @UcaseCtr = @UcaseCtr + 1
END
SET @Ctr = @Ctr + 1
END
RETURN @UcaseCtr
END
**ASCII stupid question, get a stupid ANSI !!!** -
There's still a faster way
. Cya thuesday.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply