Carriage Return - is there a better way?

thom

I recently had a request to default a "bill to" address that would be used for labels and mail merges - rather than duplicate the address lines i decided to create a calculate text field that I could build in my carriage return, and address line 2 logic.

If I was doing this in straight SQL I would build the string using the ASCII characters 10 or 13. I tried this logic in the calculated field but it did not work. I also try the reqular expression '/r'.

Ultimately, I just add data into the field and manually did the carriage return. I went to the column on the database and it had the square symbol that is the carriage return. I cut this from the column, pasted it into my rule within single quotes and it work.

Not really an eloquent solution, but it worked for the calculated address. I plan on doing some text level replaces or inclusions in the near future and am wondering if there is a better way to handle this text manipulation?

thanks

aware_support

In theory using the escaped symbol \n (new line) should work.

thom

I tried that one also (typo above i used a \r not a /r), but it could be my syntax: I have it included between by attributes as

BO.Attribute1+'\n'+BO.Attribute2

pixerp

A few months ago, I tried to achieve the same. Searching the forum, I had found this old post:

Controlled truncation of a data string>

Now that I'm not the only one to look for it, I invested a few hours again and googled for regexp.

Based on what I found in mysql documentation and at http://www.regular-expressions.info, I would expect that

BO.Attrib1+'\n'+BO.Attrib2 (or '\\n' or '\\n' !!!)

should work. So, there could be a bug ?

My further 2c., \n (hex0A) means newline, \r\n (hex0D0A) means carriage-return+newline which is equivalent to new paragraph in Word. \r alone doesn't produce a newline.

thom

Hi pixerp,

I saw that post too, which set me on the path of regular expressions. I tried the '\n', '\\n',... you had below without success when it occurred to me that maybe the regular expressions need to wrap an entire attribute, rather than a substring of one. I am unfamiliar with the details of regexp so I will play around with looking to insert a carriage return after the first address.

Whether the regexp works or not, I am a little surprised that the ASCII value CHAR(13) or CHAR(10) (for carriage return or new line) do not work in the string. Ultimately, you have to take the string a do an insert or an update which means it will be the sql cmd. So a simple select like

SELECT 'Boston'+CHAR(13)+'Red Sox'

will return the results

Boston
Red Sox

(as opposed to a single line )
Boston Red Sox

thanks

pixerp

Here is another hint I found in a forum :

"MySQL does not support regular expression for substituting in fields.
It only supports regexp for matching and finding rows.

If you want to make changes in the strings you have the string functions to choose from:
http://dev.mysql.com/doc/refman/5.1...-functions.html
Where some combination of REPLACE(), LOCATE() and SUBSTR() is the usual way.

But if you are experienced at regexp and it is hard to write the substitution with the MySQL string functions then exporting the data and writing a script in perl or php that changes it and then import it back again could be easier."

If this is true, it could mean we must use a function to insert the regexp, like REPLACE_PATTERN. But I couldn't get it to work so far.

thom

I am on SQL Server Express, but for the most part the database level functions will be the same.

Do you know a way in which I can call a database system function or UDF within a rule? This would be awesome .... i have a number of proper_case, address, name, phone #,.... database functions I have written over the years that clean or adjust these types of attributes.

In general, I understood (or mis-understood) that Aware discourages the use of Stored Procs, Triggers, & UDFs so I have restrained myself and worked with the framework.

thanks for the input

pixerp

Well, as I understand AwareIM "philosopy", you (and I) should be able to use BO rules to enforce controls and data validation at the Database level.

Apart from the difficulty raised in this post (concatenating 2 attribs wtih a newline inbetween), do you have other examples from your stored proc/triggers that you can't convert to BO rules ?

thom

Hi Pixerp,
Yes, I understand that the BO rules should enforce the controls and data validation at the database level, and some of them can be duplicated. Its just the pure volume of stuff i have that i will need to recreate within Aware.

This extends beyonds the original question and gets into a limited form of "content management", but some of the others that I will need to address beyond the NL/CR are

Openning/Closing Single Quotes
Openning/Closing Double Quotes
Trade Mark Symbol
Copyright Symbol
Proper Case
HTML to Plain Text/Plain Text to HTML (or RTF)

The way to solve this might very well be using Custom Forms/Custom Functions but I have to think somebody must have have a java based library that will address some of these string/text manipulations.

thanks for the response