Case-insensitive in SQL 7

[henjoh]

Hi,Is there anyone who can tell me how to change Uppercase to lowercase in SQL.My problem is that in installation of SQL we have choose case insensitive and if you of mistake wrote e.g ANDY and you want to change it to Andy it don´t work.Of some reason SQL remember that you have write with Uppercase and when you write it in that way you want (Andy)it still shows ANDY.Grateful for any tip!!

 

[ButtonIt]

As far as I'm aware SQL only uses the case sensitive/insensitive to sort the data. You can quite easily change the case of the data within SQL.

How are you trying to change it? Is this an entry in DNA? If so why don't you use Directory Manager?

 

[henjoh]

Hello,you can´t change it in Directory Manager so the only way to change it is in SQL directly.If you write it e.g with uppercase of mistake you can´t change it to lower case.
If you do that in DMG and you save it it still in that way that you have done first time.

 

[whosrdaddy]

from what I remember is that you have to rerun sql setup and normally from there you can change case sensitivity.

http://www.damovo.com

 

[henjoh]

I can´t rerun setup beacause i can´t lose data that I have already in database.What I am looking for is a script who change uppercase letters to lowercase letters and other way.
E.g Paul.Smith@COMPANY.COM to paul.smith@company.com

 

[whosrdaddy]

you won't loose data by rerunning setup, and if you want to be sure, just take a backup of the existing databases.

if that's not good for you, you must build a SQL query with the LOWER T-SQL function.

greetings,

http://www.damovo.com

 

[ButtonIt]

What kind of data is "ANDY"? Is it a Directory Manager entry? Is it registration information?

 

[henjoh]

ANDY is a part of an e-mailadress and I want it to be lower case instead of uppercase.Datatype of this in DMG is E-MAIL.
Is there anyone who can build a SQL query with this function
Change upper to lowercase.

 

[jerommeke]

You can do updates in SQL Servertables directly,
but a backup of all is always a good idea.
Make sure you're able to restore a backup to !!!

The SQL syntax looks like

UPDATE {table}
SET field = lower (field)

will result in all data in field 'field' will be converted to lowercase.

Normally the online SQL books are accessable through start->programs->MSSQL....

The exact datamodel of DMG is been lost in a prior life , but you'll have to find out where the data you want to change is stored. Enterprise manager will allow you to see the fieldnames and table for the databases.

The next step will be an forced export to all the clients,
OPI, EMG etc. If I remember correct there is an export-button in one of the admin tools of Application Suite.

\\Jerommeke

 

[henjoh]

Thanks a lot for the answer. Do you now how I can easily change this to. Of mistake I wrote lonDoN and I want to change this to London.

 

[jerommeke]

Keep telling be carefull , but it can be done by using:

UPDATE {table}
SET {field} = 'London'
WHERE {field} = 'lonDoN'

Note that all records matching the WHERE clause will be changed.
You may want to add extra restrictions using AND :

UPDATE {table}
SET {field} = 'London'
WHERE {field} = 'lonDoN'
AND {field2} = 'John'

 

[henjoh]

Hi Jerommeke
Thanks a lot for the scripts!!! /henjoh