Another simple scripting question.

Question

Post reply

Another simple scripting question.

Graham Knight

SSC Rookie

Points: 41
More actions
June 5, 2007 at 9:22 am

#179105

We have an Application that uses an SQL database unfortuneatly the users of this application are not filling in fields correctly (don't ask..... it's too complicated). I am new to SQL databases but I have some VB experience, what I want to do is to write a script in SQL Server 2005 and check records for a value say 'haverhill' once I have all these records I then want to change 'haverhill' to 'Haverhill'. I imagine this must be possible I just need the syntax for SQL, as I see it it's completely different to the Jet database engine I use in VB.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 1

UPDATE <table_name>

SET <field_name> = 'Haverhill'

WHERE <filed_name> = 'haverhill'

The Redneck DBA

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 2

I have something implemented that might work for you. It allows you to define standard names with any number of alternate names for each one. A function then returns the standard name if passed any of the alternate names. Assuming we've defined all the variations of the name 'William', it looks like this:

Select dbo.GetStandardName('bill');       -- returns 'William'
Select dbo.GetStandardName('will');       -- returns 'William'
Select dbo.GetStandardName('WILLIAM');    -- returns 'William'
Select dbo.GetStandardName('NotDefined'); -- returns 'NotDefined'

So you can use this to filter data as it goes in:

Insert into table (...) values (..., dbo.GetStandardName(data), ...)

or accessing the data:

Select ..., field, dbo.GetStandardName(field) as AltField, ....

An enhancement I made just recently was to define a context for the names. So 'sam' might return 'Samual' for a "MaleName" context, 'Samantha' for a "FemaleName" context or 'Surface to Air Missile' for a "Weapon" context. You, of course, would define your own contexts.

I was going to paste all the DDL but there are two tables and two procedures and that is a lot of lines to put here. If it sounds like something that would serve your purpose, I would be happy to send it.

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

Graham Knight SSC Rookie Points: 41 More actions · Answer 3

Vewry many thanks for your help so far. I have got the change bit to work, how can I report on what has been change. Presumably a would need some kind of storage and then print from the storage.