[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 417
  • Last Modified:

Can UDF be used as a default action for a Column? Same concept as Referential Integrity!

Can a User Defined Function such as a scalar UDF to return only clean characters?
For example, select nm_frst, NM_LST, DB2ADMIN.UDF_CleanChar('a@b#c$d%e%f^g')  should return only "abcdefg".
I want to try to automatically trigger a background call to this UDF_Clean_Char whenever a user enters data in selected application fields.  The key to this question and points:
 
 Is that possible? How can it be done?

Thanks

0
Enuda
Asked:
Enuda
  • 4
  • 4
2 Solutions
 
Dave FordSoftware Developer / Database AdministratorCommented:

I've found the easiest way is to use the TRANSLATE function. Once I identify the specific characters that I want to remove, it's easy enough to write an update statement that removes them. That update could then be packaged into a UDF.

update MyTable
set       text = translate(text,'', '@#$%^')  

Alternately, if it's one one or two characters I want to remove, I've used the REPLACE function.

HTH,
DaveSlash
0
 
EnudaAuthor Commented:
Hi DaveSlash,
Thanks for the response. Let's see if I understand you.
In my application code will be a executable statement that says "SET TEXT=TRANSLATE(TEXT,'@#S%'?
I take it TRANSLATE will return the character "S"? If so that might solve my problem. Let me check out TRANSLATE. I don't even know we have that in UDB...

Thanks
0
 
tliottaCommented:
Note the DaveSlash's example had [ (text,'', '@#$%^') ] where the second parameter was a null string. The translate() function "translates" the characters in the third parameter to the character in the second parameter, effectively cutting those characters out of the text string.

Tom
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
EnudaAuthor Commented:
I tried it and that is not what I want to do..the UDF must be able toeliminate everything that is nor A-Z or 0 thru 9...thanks for the enlightenment...and that goes for points too!
0
 
tliottaCommented:
Enuda:

Then you simply use a translate constant that contains all characters except A-Z and 0-9. The sample constant only had the characters '@#$%^'. You can put as many different characters in there as you need. Since we don't know what character set you might need, we don't know what the string might be.

A larger example would be --

  (text,'', '~!@#$%^&*()_+=-{}|\][:;<>,./?abcdefghijklmnopqrstuvwxyzºâãäåæçèéêë')

Put whatever characters you need to eliminate in there.

Tom
0
 
EnudaAuthor Commented:
tliotta,
Yes, I agree with you. But consider trying to cover all known and possible non-english characters like the Chinese, Japan, arabic...you get the picture? Translate is just not the tool to use in this case...
On the other hand, is there a UDF that that does the opposite like check to see if the source word/characters is withing A-Z or 0-9? If any character is not found, reject the input...not user friendly I might add...
0
 
tliottaCommented:
Enuda:

Yes, that's where not knowing what character set causes trouble for a precise answer, not to mention not knowing what DB2 is involved.

Now, if the result should be "reject the input", then TRANSLATE() can still be the answer. Consider the opposite result:

  translate(text,'', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ 0123456789')

If text contains only A-Z, 0-9 and blanks, then the translated result will necessarily be a null string. That is:

 if translate(text,'', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ 0123456789') = ''

...is a valid IF-test to see if only those characters were in text. When some other character is in the text string, the result will be just the unwanted characters. By coding this way:


 if translate(text,'', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ 0123456789') <> ''

...you have an IF-test that determines whether to "reject the input" or not.

Tom
0
 
EnudaAuthor Commented:
I agree completely. I think I will try to persuade the developer to look at it from this point of view. The end result is to get clean alphanumerics within known/accepted english characters.
Thank you for sharing your thoughts on this...
0
 
tliottaCommented:
Minor added note...

  set  xlatechars = translate(text,'', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ 0123456789')

This should give a value in xlatechars that consists _only_ of characters from text that are not in the set of desired characters. If your developer can go to the next level and work with dynamic SQL, it becomes possible to use xlatechars in a subsequent statement like:

  set  text = translate(text,'', xlatechars)

I.e., you don't technically need to know which characters to cut out. You can create a string of those characters from the first translate(). Then a second translate() can use what was found to clean up the original string.

One significant issue is that the /from-string/ needs to be a constant rather than a variable. (At least, it does in the DB2 that I use.) That means that the second translate() would be constructed and executed as a dynamic statement. If this is directed at user input, maybe that's not an issue. If it's directed at queries over many columns in many rows, then performance could be a problem.

And maybe there are good ways around it in whatever DB2 you're using.

Regardless, once the trouble characters are known and stored in xlatechars (or whatever variable name is desired), then UDF coding can do whatever is needed even if builtin functions aren't used.

Tom
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now