?
Solved

sql 200 finding lower case letters within a column

Posted on 2011-03-09
4
Medium Priority
?
637 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:g_johnson
  • 2
4 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35086356
Is your database CS (Case Sensitive)?
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35086457
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 35086537
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
 
LVL 4

Author Closing Comment

by:g_johnson
ID: 35086985
Perfect.  Thanks.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question