Sql remove null character

One aspect of transforming source data that could get complicated relates to the removal of ASCII special characters such as new line characters and the horizontal tab. In this article, we take a look at some of the issues you are likely to encounter when cleaning up source data that contains ASCII special characters and we also look at the user-defined function that could be applied to successfully remove such characters.

Script 1 shows us an example of how an ASCII numeric code 92 can be converted back into a backslash character as shown in Figure 1. Say for instance that source data contains an email address for John Doe that has several invalid special characters as shown in Script 2. Execution of Script 3 results into a correctly formatted email address that is shown in Figure 2. A preview of the output. Furthermore, if you go back to Script 4 , you will recall that for the 3 rd email address, I included the start of header character at the end of the email address, but looking at the data in Figure 3 , the start of header character is not easily visible at the end of that 3 rd email address.

In fact, it looks like the email address 3 and 4 have the same amount of characters — which is not true. Learn about this website's comments policy here. Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.

The value s to be translated. In this case A upper case A to z lower case z includes all of the alphabetic characters regardless of case. In this case I want to translate all of the letters to blank. This article was written for IBM i 7. Labels: programming , sql. Dan Doorn October 10, at PM. Anonymous February 3, at AM.

Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog. Podcast Helping communities build their own LTE networks. Podcast Making Agile work for data science. Featured on Meta. New post summary designs on greatest hits now, everywhere else eventually. Linked Related Hot Network Questions. Question feed. Stack Overflow works best with JavaScript enabled.