Solved

sql 200 finding lower case letters within a column

Posted on 2011-03-09
4
623 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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.

730 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