Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Ready
Starting Member
3 Posts |
 Posted - 2002-06-21 : 11:14:57
|
| I have a large DB with several fields i wish to select from, i have managed to select all the fields i require however the output format is not what i hoped. What is happening is that the address field created is Varchar 200, the input form alows the user to enter this data into 1 message box - ie. :-MyStreetMyAreaMyCountyMyCountry Etc, all the data is then put into 1 db field (address). I gues you can see whats happening here, when the search returns the data it is displaying the carriage returns and therefore messing up the the next set of data returned.My long winded question is this - is there a funciton i can call in the select statement which will remove the carriage returns or the blank spaces in the address field ? |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-21 : 11:21:30
|
| look up replace() in books onlinesetBasedIsTheTruepath<O> |
 |
|
|
Ready
Starting Member
3 Posts |
Posted - 2002-06-21 : 12:06:22
|
| I forgot to mention that the SQL verison is 6.5 ! - The replace function is not available.Any other ideas ? Thanks again. |
|
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2002-06-21 : 12:13:19
|
quote:
Etc, all the data is then put into 1 db field (address).
That's not a good idea. It goes against the basis of relational database design - normalisation.Consider separate database fields for each line of the address.This will allow greater ease/speed in sorting and restricting the data.Eg. SELECT * FROM myTable WHERE MyCountry = 'Japan'instead of SELECT * FROM myTable WHERE Address like '%Japan%'That aside, REPLACE(REPLACE(Address,char(13),''), char(10), '') will remove any line feed and carriage returns in the data of Address.REPLACE(Address, ' ', '') will remove the blank spaces. So you should get Address------------------------------------------------2FairviewPlaceOceanViewMarinUSA |
|
|
|
Ready
Starting Member
3 Posts |
Posted - 2002-06-21 : 12:21:31
|
| I totaly agree with the data relationship model and DB fails in first normalisation in many places, however believe it or not this is a MOD database and was written under strict guide lines ! - abit a few years ago.Any ideas on how to do this on SQL 6.5 ?Edited by - ready on 06/24/2002 02:36:12 |
|
|
|
DGMelkin
Starting Member
24 Posts |
Posted - 2002-06-21 : 13:27:16
|
Try this: SELECT substring(Address, 1, charindex(char(13), Address) - 1) + substring(@blah, charindex(char(13), Address) + 1, len(Address))FROM yourtable Feel free to adjust as needed. I've had to replace a character with another in SQL6.5 before and this seemed to work. |
|
|
|
MakeYourDaddyProud
184 Posts |
Posted - 2002-06-24 : 06:59:27
|
| Correct me if i am wrong, but melkins solution assumes two CRs and any remaining ones are ignored.I see you mentioned a Form is used to enter the data, Is there anyway you could recode your app to split the address parts into seperate fields? I appreciate this might not help your current problems but I am sure I have a VB class module that turns 1 line addresses with CR/LFs into seperate line ones if you are interested...Dan www.danielsmall.com IT Factoring |
|
|
|
|
|
|
|
|
Select Statement help