sql 200 finding lower case letters within a column

I have a need to correct some data and am planning on using the attached sp.  However, ascii(variable) only compares the first character of the column value.  How can I change this so that it finds the problem if ANY character is not upper.  For example, I need to find 10722WWst on the basis that s and/or t is not upper case.

Please note that the 'and' part of the where clause for the cursor is there just to limit this for testing.


Thanks
alter proc CorrectJobNos
as
	declare @tbl sysname
	declare @tblName varchar(20)
	declare @sql nvarchar (4000)

	declare tblCur cursor static local for
		select			o.name,o.name
		from			syscolumns c
		join				sysobjects o
			on				c.id = o.id
		where			c.name = 'job_no'
		and				o.name = 'GaryTestJobs'
		order by		o.name
	open tblCur

	while 1 = 1
	begin
		fetch tblCur into @tbl,@tblName
		
		if @@fetch_status <> 0
			break
		
		select @sql = 
		N' insert into			JobNoChangeAudit' +
		N' select distinct		''' + @tblName + ''',t.job_no' +
		N' from					' + quotename(@tbl) + ' t' +
		N' where				ascii(job_no) != ascii(upper(job_no))'
		
		select @sql
		
		exec sp_executesql @sql
		
		select @sql =
		N' update			' + quotename(@tbl) +
		N' set				job_no = upper(job_no)' +
		N' where			ascii(job_no) != ascii(upper(job_no))'
		
		exec sp_executesql @sql
	end
	
	deallocate tblCur

Open in new window

LVL 4
g_johnsonAsked:
Who is Participating?
 
Anthony PerkinsCommented:
If it is not CS it should be as simple as using something like this:
CAST(UPPER(job_no) AS varbinary(200)) != CAST(job_no AS varbinary(200))
0
 
Anthony PerkinsCommented:
Is your database CS (Case Sensitive)?
0
 
LowfatspreadCommented:
try this

as long as job_no < 2048 characters....


i think master.dbo.spt_values existed in sql 2000....

if not then replace it with the old standby

(
Select x+(10*x1)+(100*x2)+... as Number
  from (
select 0 as x union all select 1 union all  select 2 union all select 3 ... union all select  9) as x
,(
select 0 as x union all select 1 union all  select 2 union all select 3 ... union all select  9) as x1
,(
select 0 as x union all select 1 union all  select 2 union all select 3 ... union all select  9) as x2
...
) as V
alter proc CorrectJobNos
as
	declare @tbl sysname
	declare @tblName varchar(20)
	declare @sql nvarchar (4000)

	declare tblCur cursor static local for
		select			o.name,o.name
		from			syscolumns c
		join				sysobjects o
			on				c.id = o.id
		where			c.name = 'job_no'
		and				o.name = 'GaryTestJobs'
		order by		o.name
	open tblCur

	while 1 = 1
	begin
		fetch tblCur into @tbl,@tblName
		
		if @@fetch_status <> 0
			break
		
		select @sql = 
		N' insert into			JobNoChangeAudit' +
		N' select distinct		''' + @tblName + ''',t.job_no' +
		N' from					' + quotename(@tbl) + ' t' +
		N' where exists (select ''Y'' from master.dbo.spt_values as v ' +
		N' where v.type=''p'' and v.number between 1 and len(job_no)' +	
		N'  and ascii(substring(job_no,v.number,1)) <> ascii(upper(substring(job_no,v.number,1))) '+
		N' )'
		
		select @sql
		
		exec sp_executesql @sql
		
		select @sql =
		N' update			' + quotename(@tbl) +
		N' set				job_no = upper(job_no)' +
		N' where exists (select ''Y'' from master.dbo.spt_values as v ' +
		N' where v.type=''p'' and v.number between 1 and len(job_no)' +	
		N'  and ascii(substring(job_no,v.number,1)) <> ascii(upper(substring(job_no,v.number,1))) '+
		N' )'
		
		exec sp_executesql @sql
	end
	
	deallocate tblCur

Open in new window

0
 
g_johnsonAuthor Commented:
Perfect.  Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.