6 Replies - 987 Views - Last Post: 03 April 2017 - 03:41 AM Rate Topic: -----

#1 dag72  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 197
  • Joined: 02-March 11

End of line character

Posted 23 November 2016 - 05:34 AM

Hey guys I'm having some issues getting exported my resultset exported from amazon redshift to aligned correctly in excel. I have established the problem to be an end of line character '10' so simple trying to replace that with '' but still doesn't resolve the issue. My code is below. Any suggestion please?

select 
replace(id,'10','')as id,
replace(name,'10','')as name,
replace(ppl_id__c,'10','') as ppl_id__c,
replace(record_type__c,'10','')as record_type__c,
replace( billingstreet,'10','') as billingstreet,
replace(billingstate,'10','') as billingstate,
replace( billingpostalcode,'10','') as billingpostalcode,
replace( billingcountry,'10','') as billingcountry,
replace(createddate,'10','')as createddate,
replace(country_code,'10','') as country_code,
replace(registrant_code,'10','')as registrant_code,
replace(full_isrc_code,'10','')as full_isrc_code,
replace(isrc_source,'10','')as isrc_source
from mdap.isrc_consolidated




Thanks

Is This A Good Question/Topic? 0
  • +

Replies To: End of line character

#2 maceysoftware  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 348
  • View blog
  • Posts: 1,493
  • Joined: 07-September 13

Re: End of line character

Posted 23 November 2016 - 05:39 AM

My SQL isn't great, so maybe I am missing something that is really obvious to other people however.

Lets take your first replace command:
	replace(id,'10','')as id,



Your replacing the string 10 with a empty string, you are not replacing the end of line character which in this case is (10)

I believe what you want is:

	replace(id,CHAR(10),'')as id,


However like I said my SQL isn't great so I may be missing something here.
Was This Post Helpful? 1
  • +
  • -

#3 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 4136
  • View blog
  • Posts: 13,051
  • Joined: 08-June 10

Re: End of line character

Posted 23 November 2016 - 05:51 AM

View Postdag72, on 23 November 2016 - 01:34 PM, said:

I have established the problem to be an end of line character '10' so simple trying to replace that with '' but still doesn't resolve the issue.

10 is not the end-of-line character. 10 is the (decimal) Unicode code point of one end-of-line character (specifically the 'line feed' (LF) character, the other one being the 'carriage return' (CR) at code point 13)
Was This Post Helpful? 0
  • +
  • -

#4 dag72  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 197
  • Joined: 02-March 11

Re: End of line character

Posted 23 November 2016 - 05:57 AM

thanks for replying I will give this a go and see.
Was This Post Helpful? 0
  • +
  • -

#5 dag72  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 197
  • Joined: 02-March 11

Re: End of line character

Posted 23 November 2016 - 06:59 AM

Thanks again. This solve the issue. Only am using postgrelsql

so I had to replace
replace(id char(10),'') as id,

with


replace(id chr(10),'') as id,




so the difference in syntax is 'char', and 'chr'

Many thanks
Was This Post Helpful? 0
  • +
  • -

#6 Sam Hobbs  Icon User is offline

  • D.I.C Head

Reputation: 11
  • View blog
  • Posts: 50
  • Joined: 01-April 17

Re: End of line character

Posted 03 April 2017 - 02:12 AM

View PostDormilich, on 23 November 2016 - 05:51 AM, said:

I have established the problem to be an end of line character '10' so simple trying to replace that with '' but still doesn't resolve the issue.

I know your problem is solved but here are some relevant articles.


View Postdag72, on 23 November 2016 - 01:34 PM, said:

10 is not the end-of-line character. 10 is the (decimal) Unicode code point of one end-of-line character (specifically the 'line feed' (LF) character, the other one being the 'carriage return' (CR) at code point 13)

Line feed is end-of-line in Unix/Linux. That is why in C/C++ we use just "\n" at the end of a line for all C and C++ standard functions. In Apple computers, it is just a carriage return (CR) at the of lines except I have heard that newer versions of Apple operating systems are consistent with the rest of the world. CRLF is the standard for the internet (at least email for sure) but it is not the standard for all operating systems. See Difference between CR LF, LF and CR line break types? - Stack Overflow for example.

This post has been edited by Sam Hobbs: 03 April 2017 - 02:18 AM

Was This Post Helpful? 0
  • +
  • -

#7 dag72  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 197
  • Joined: 02-March 11

Re: End of line character

Posted 03 April 2017 - 03:41 AM

View PostSam Hobbs, on 03 April 2017 - 02:12 AM, said:

View PostDormilich, on 23 November 2016 - 05:51 AM, said:

I have established the problem to be an end of line character '10' so simple trying to replace that with '' but still doesn't resolve the issue.

I know your problem is solved but here are some relevant articles.


View Postdag72, on 23 November 2016 - 01:34 PM, said:

10 is not the end-of-line character. 10 is the (decimal) Unicode code point of one end-of-line character (specifically the 'line feed' (LF) character, the other one being the 'carriage return' (CR) at code point 13)

Line feed is end-of-line in Unix/Linux. That is why in C/C++ we use just "\n" at the end of a line for all C and C++ standard functions. In Apple computers, it is just a carriage return (CR) at the of lines except I have heard that newer versions of Apple operating systems are consistent with the rest of the world. CRLF is the standard for the internet (at least email for sure) but it is not the standard for all operating systems. See Difference between CR LF, LF and CR line break types? - Stack Overflow for example.


Many thanks for this info
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1