Good Afternoon, please help I have an address field in my database and I need to convert the data from Upper case to proper. Does anyone have an easy way to do this. Here is my select statement.
elect rtrim(p.scode) pscode, u.scode uscode, rtrim(ad.sAddr1) u_addr from property p inner join unit u on (p.HMY = u.hproperty) left outer join ADDR ad on (ad.HPOINTER = u.HMY and ad.ITYPE = 4) where u.HPROPERTY = 'propcode' and u.exclude <> -1 and ad.SADDR1 like '%CHARLES%'
Here is my initial update statement because I also need to be able to add in "steet, drive, ect... but how do I go about making the street name proper case first?
begin tran UPDATE Addr SET sAddr1 = RTRIM(sAddr1)+' '+'Drive' FROM ADDR A INNER JOIN UNIT U ON U.hmy = A.hPointer AND a.itype=4 WHERE u.hproperty=propcode AND u.exclude<>-1 AND a.saddr1 LIKE '%CHARLES'
The change is needed in the database it's not just for display purposes. There is too much data to try and make the change directly in the database itself it would be easier and less time consuming if there is a way to make the change in SQL to the database.
There can be a number of reasons for doing this - everything from having no front end application, or having only a thin client front end. Even in case insensitive collations, while logical comparisons are case insensitive, the storage itself is case sensitive.