Solved

sql 200 finding lower case letters within a column

Posted on 2011-03-09
4
621 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 500 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

809 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