Solved

sql 200 finding lower case letters within a column

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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.

839 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