Checking for at least one upper case

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Checking for at least one upper case

Author

Topic

dumbdumb
Starting Member

5 Posts

Posted - 2004-03-16 : 23:18:33

Hi all,

All this is in store procedure.
How do I search for whether an input parameter conatain at least one upper case? Return true if there is else return false.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-16 : 23:36:00

[code]
--create tally table for the function
set nocount on
create table dbo.numbers ( n int primary key)
declare @n int ; set @n = 1
while @n <=255 --max length of input parameter
begin
insert into numbers
select @n
set @n = @n + 1
end
GO

--Input
declare @input varchar(20)
set @input = 'hello'

--Function
declare @bool bit
set @bool = 0
IF EXISTS
(
select 1
from dbo.numbers
where n <=len(@input)
and convert(binary,(UPPER(substring(@input,n,1)))) = convert(binary,substring(@input,n,1)) and substring(@input,n,1) like '[A-Z]'
)
set @bool = 1

select @bool
GO
[/code]

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-03-16 : 23:50:33

If you are using SQL2K you can leverage the COLLATE function...


DECLARE @T VARCHAR(1000)
SET @T = 'asdfasdfs'

IF PATINDEX('%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%' COLLATE latin1_general_cs_as, @T) > 0
	PRINT 'OK'
ELSE
	PRINT 'No'
--OR
IF @T LIKE '%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%' COLLATE latin1_general_cs_as
	PRINT 'OK'
ELSE
	PRINT 'No'

EDIT: The LIKE version.....

DavidM

"An Ugg Boot is a generic Australian term that has been in use for nearly 100 hundred years. Now some coporate wanker has trademarked it.. "

dumbdumb
Starting Member

5 Posts

Posted - 2004-03-17 : 00:56:30

Hi

Hey thanks ya! Your code works! Hmm....but now I have another problem, This chunk of code does not work when applying for symbol!
Symbol definition : Any character that is not alphanumeric and number


DECLARE @T VARCHAR(1000)
SET @T = 'asdfasdfs'

IF PATINDEX('%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%' COLLATE latin1_general_cs_as, @T) > 0
	PRINT 'OK'
ELSE
	PRINT 'No'
--OR
IF @T LIKE '%[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%' COLLATE latin1_general_cs_as
	PRINT 'OK'
ELSE
	PRINT 'No'

EDIT: The LIKE version.....

DavidM

"An Ugg Boot is a generic Australian term that has been in use for nearly 100 hundred years. Now some coporate wanker has trademarked it.. "
[/quote]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources