June 23, 2003 at 1:12 pm
Hello,
I have a field called 'StreetName' in a table called 'tblSBStreetData' in which all of the records in the field should be upper case. I once had a Trigger implemented to take care of this need, but the presence of an additional Trigger on the same table was causing some problems.
I have an SP that runs in conjunction with a job during off hours to clean up the data in this table. Part of that SP needs to include an UPDATE to the 'StreetName' field that will Update only the records where any character in the field is lower case. I have tried a couple of things so far, but when I test a single record, the UPDATE updates all of the records or none of the records.
The following is an UPDATE statement with a series of WHERE conditions that I have tried, but have not worked:
UPDATE tblSBStreetData
SET Streetname = UPPER(StreetName)
--WHERE Streetname = LOWER('%')
--WHERE Streetname = LOWER(Streetname)
--WHERE Streetname = (SELECT LOWER (SUBSTRING(Streetname, 1, LEN(Streetname)))FROM tblSBStreetData)
--WHERE EXISTS(SELECT CHARINDEX(LOWER(LEN(Streetname)),Streetname,0)FROM tblSBStreetData)
How do I modify this UPDATE statement so that only the records with any number of lower case characters will be updated to UPPER(StreetName)? I don't want the UPDATE to scan all of the records in the table.
Thanks!
CSDunn
June 24, 2003 at 1:35 am
Try making the Where clause:-
WHERE UPPER(STREETNAME) <> STREETNAME
June 24, 2003 at 2:37 am
Preview
if your server is case insensitive (the default), you could make the WHERE criterion:
BINARY_CHECKSUM(Streetname) <> BINARY_CHECKSUM(UPPER(Streetname))
Cheers,
- Mark
June 24, 2003 at 9:53 am
The column is not case sensitive. Maybe I should change the collation of the column?
CSDunn
quote:
Try making the Where clause:-WHERE UPPER(STREETNAME) <> STREETNAME
June 24, 2003 at 11:24 am
Your query will have to scan the whole table anyway.
But cast(streetname as varbinary) != cast(upper(streetname) as varbinary) would work to restrict the update to the columns you want.
As McCork says, you can use BINARY_CHECKSUM - for SQL 8+ (I had to retype that '8' three times, because I kept typing '*' - SQL overdose!).
I would be inclined to get your trigger working, though. If you are having problems with multiple triggers, why not just combine them into one?
Tim
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply