November 24, 2003 at 3:34 pm
I have to tidy up loads of different data and have become stuck on one issue. I have imported data from Access into SQL, one of the columns 'Address1' had lots of carriage returns, in SQL these are now double spaces. I'm trying to split this data into separate fields i.e address2, city etc. e.g
1.)10 City Place London EC2
2.)Regatta Place London EC2
Using either the CHARINDEX or the PATINDEX function, Example 1 does not find double space between Place and London but in example 2 it does find double space between Regatta and Place.
I'm sure this is something obvious I have missed. Any help appreciated. Thanks
November 24, 2003 at 4:03 pm
Have you though of
update data = REPLACE(data,' ',char(9))
select substring(data,CHARINDEX(char(9),data) ,len(data)) -- will give you the 2nd part of address you are looking for.
mom
November 25, 2003 at 2:16 am
you may need to repeat MOM's tip but using CHAR(10) - Line Feed and CHAR(13) - Carriage return. CHAR(9) is TAB.
On the other hand if you can preserve these characters in the SS table after uploading from Access you might be able to search for them and split the fields out that way.
November 25, 2003 at 4:19 am
When you use 'select * sometable' to display the table the cr/lf show as 2 spaces even though they are stored in the table as CHAR(10) + CHAR(13). Open the table using Enterprise Manager and view the column contents. You should see the contents taking up multiple lines - make the row high bigger in the display grid to show multiple lines at the same time.
November 25, 2003 at 9:45 am
I just use char(9) because it's easy for me to see when I bcp it out to text:) for your purpose use anything you like, It'll work just as greate.
mom
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply