Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 8 of 8 total |
Trying to REPLACE #13 CHARs in SQL |
Sat, Feb 13 2010 1:57 PM | Permanent Link |
adam | I have some memo fields which have been filled with data, but too many carriage returns.
This is the result of concatenating a series of VARCHAR fields without thinking things through properly, the empty fields were added as additional returns, not by me, I am not sure exactly how. Now we have many records with poorly formatted data. The issue is that I do not want to remove ALL the carriage returns, only when they are duplicated. i.e. the data should be in the form 1 High St Some District SomeTown NOT 1 High St Some District SomeTown OR 1 High StSome DistrictSomeTown There are several 10k of data. -- I want to run SQL in the form: UPDATE Addresses SET StreetDetails = REPLACE(#13#13 WITH #13 IN StreetDetails); ... but this does not work, nor does: UPDATE Addresses SET StreetDetails = REPLACE('#13#13' WITH '#13' IN StreetDetails) -- - How should I express this? - How can I easily see the CHAR contents of the memo field, to assess what non-visible characters it contains? Thanks Adam |
Sun, Feb 14 2010 4:08 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | adam
You need a plus between each #character. I'd also suspect that its crlf rather than just cr so update Addresses set StreetDetails= replace(#13+#10+#13+#10,#13+#10,StreetDetails) Roy Lambert[Team Elevate] |
Sun, Feb 14 2010 5:13 AM | Permanent Link |
adam | Thank you Roy, I would have torn my hair out before I would have figured that out ...
though probably it is in the help files somewhere! |
Sun, Feb 14 2010 10:40 AM | Permanent Link |
adam | Sorry Roy, your help has't got me anywhere ... I am starting to suspect there may be other
non-visible characters present in the field. New question: Any way I can see the CHAR contents of a MEMO field, to examine exactly what non-printing chars are present? |
Sun, Feb 14 2010 11:13 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | adam
If the memos aren't to big what I used to do was open in Delphi and loop through dumping the intostr(counter) & inttostr(Ord(memofield.asstring[counter])) to a memo component. As an added refinement you can just do that if memofield.asstring[counter]< #32. Roy Lambert [Team Elevate] |
Mon, Feb 15 2010 12:13 PM | Permanent Link |
"Rita" | Adam try copying the memo then
paste it into notepad. Look to see any characters there. If not open Wordpad paste it there and check. I download a csv file daily and it contains a little square I guess a carrige return if I view it in notepad. So I save it as a txt then open it in Wordpad the characters go and I have a nice text delimited file. Not what you want but maybe you can see the same little square. As another bonus I have the racing SP's in one long string upto 30 sometimes 75 lines such as below. 5/4 Alpha, 6/4 Bravo, 2/1 Charlie, 3/1 Delta ++++++++ I need to get them into a text delimited file I just go to wordpad after copying the little square do a replace and the above all go to 5/4 Alpha 6/4 Bravo 2/1 Charlie 3/1 Delta Hundreds then I get a comma between in another way to seperate into 2 fields. HTH "adam" <adam@fullwellmill.co.uk> wrote in message news:F7E413DC-46E2-4C8D-8C35-4C558C68997F@news.elevatesoft.com... > Sorry Roy, your help has't got me anywhere ... I am starting to suspect > there may be other > non-visible characters present in the field. > > New question: > > Any way I can see the CHAR contents of a MEMO field, to examine exactly > what non-printing > chars are present? > |
Mon, Feb 15 2010 12:21 PM | Permanent Link |
"Rita" | Forgot to attach file to see if it
looks same on everyone elses computer the little square is visible in notepad but goes in Wordpad Rita "Rita" <nospam@nospam> wrote in message news:ADFAC29C-2C04-4D61-AE7C-60728575FF87@news.elevatesoft.com... > Adam try copying the memo then > paste it into notepad. Look to see > any characters there. > If not open Wordpad paste it there > and check. > I download a csv file daily and it contains > a little square I guess a carrige return if > I view it in notepad. So I save it as a txt > then open it in Wordpad the characters > go and I have a nice text delimited file. > Not what you want but maybe you can see > the same little square. > As another bonus I have the racing SP's > in one long string upto 30 sometimes > 75 lines such as below. > 5/4 Alpha, 6/4 Bravo, 2/1 Charlie, 3/1 Delta ++++++++ > I need to get them into a text delimited file > I just go to wordpad after copying the little > square do a replace and the above all go to > > 5/4 Alpha > 6/4 Bravo > 2/1 Charlie > 3/1 Delta > > Hundreds then I get a comma between in another way > to seperate into 2 fields. > HTH > > > "adam" <adam@fullwellmill.co.uk> wrote in message > news:F7E413DC-46E2-4C8D-8C35-4C558C68997F@news.elevatesoft.com... >> Sorry Roy, your help has't got me anywhere ... I am starting to suspect >> there may be other >> non-visible characters present in the field. >> >> New question: >> >> Any way I can see the CHAR contents of a MEMO field, to examine exactly >> what non-printing >> chars are present? >> > > Attachments: aaa.txt |
Mon, Feb 15 2010 1:18 PM | Permanent Link |
"Robert" | "Rita" <nospam@nospam> wrote in message news:ADFAC29C-2C04-4D61-AE7C-60728575FF87@news.elevatesoft.com... > Adam try copying the memo then > paste it into notepad. Look to see > any characters there. There are plenty of free programs to examine the characters in a file. google view characters in file Robert |
This web page was last updated on Wednesday, June 12, 2024 at 01:54 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |