SQR: Removing Special characters from a String

remove special characters in SQRThis week was hectic! Though we had a very normal start, a couple of critical job failures knocked us mid week taking away all our coffee breaks!

Once the jobs were fixed, it was time for me to test the SQR that I completed last week. The requirement for this came from the client as some queries when run manually (on TOAD) were taking long and they liked to get it converted into SQR and scheduled overnight!

Let me remind you that the client is on PeopleSoft 7.0, SQR 3.0 and all their jobs are scheduled from a third party tool called Autosys. And NO, they are not thinking of an upgrade!
We have been associated with this client for just over 2 months now and this is our first development activity for them. We were all new to this old version and were learning the nuances of it.

We were telling ourselves that writing this SQR is going to be simple as we already had the queries with us. But it didn’t work out quite well as we thought. We started realizing it only once the testing began. Most of the functions I wrote were not supported on this version of SQR! I ended up getting most of my date calculations done in the database with the help of BEGIN-SELECT.

Finally we managed to generate the report which was in a .csv file. I used FTP to take the report out of the repository and had a look at it. There was a mess towards the right end of the report. And also there were also some misplaced lines! A quick investigation reminded us of a possibility we had overlooked. One of the fields that we had in the query was 250 char long description filed. There were some special characters in the field which caused the trouble.

This included some commas which caused the rest of the text to be treated as the next column (when opened through excel) and also some other characters that caused the rest of the text to be treated as a new line!

The solution: remove them all before putting them to the report. And here’s how we nailed it.

!Code for removing special characters from a string
!Here we use the ideology that we have a finite set of
!valid charecters (alpha numeric) while the set of special
!characters is fairly big
!$Descr will give the final string without special chars.
 
LET $valid_chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz -./0123456789"'
!These are the only characters that will be left in the string.
 
LET $invalid_chars = translate(&DESCR254, $valid_chars, '')
!Finding the special characters that are in the string by removing 
!each occurance of the valid characters fromt the string.
 
LET #invalid = length($invalid_chars)
!Getting the count of invalid characters
 
IF #invalid !IF there were some invalid characters
	LET $Descr = translate(&DESCR254, $invalid_chars, '')
	!Replace all invalid characters in the source string with NULL
 
ELSE !If there were no invalid characters
	LET $Descr = &DESCR254
	!Copy source string to output string as there were no special chars 
END-IF

You can download the code here.

Tags: , ,
Subscribe to Comments RSS Feed in this post
14 Responses
  1. One of my friends already told me about this place and I do not regret that I found this article.

  2. Fantastic post, many interesting points. I believe 6 of days ago, I have saw a similar blog. Does anyone know how to track future posts?

  3. great post as usual!

  4. Hey, I view all your posts, keep them coming.

  5. Great Blog! Very informative, I appreciate all the information that you just shared with me very much and I also bookmarked this on dig too. Take care and I’ll be back to read more in the future.

  6. I really like when people are expressing their opinion and thought. So I like the way you are writing

  7. @Angeles
    You may subscribe to the RSS.

  8. This post helped me alot in my college assignment. Thank you for your information.

  9. Thanks for the info

  10. This post helped alot .Thanks for the information.

  11. I am trying to read data from a fixed format file into records using file layout. But the file contains certain fields as NUL characters. I had tried to read each line store it in a string and check each character in it is a null value or not, if it is a null I am trying to replace it with a space. But here all the data in the line is not being read into the string, it is skipping some data because of the presence of NUL characters.

    How do I overcome this issue ??

  12. This was exceptionally helpful. I am grateful you shared this and am subscribing. THANK YOU

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

Read previous post:
PeopleSoft Journal Uploader
PeopleSoft Journal Import Error – User-defined type not defined

Close