Post reply

Another simple scripting question.

  • 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.

  • June 5, 2007 at 12:53 pm

    #710936

    UPDATE <table_name>

    SET <field_name> = 'Haverhill'

    WHERE <filed_name> = 'haverhill'

    The Redneck DBA

  • June 6, 2007 at 6:33 pm

    #711285

    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

  • June 11, 2007 at 7:58 am

    #712000

    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.

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

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