I am tring to clean up data in some of our fields. The requirement is
to only have letter (a-z,A-Z) and number (0-9) characters in the
field...
So, if we select from the non-cleaned-up field, we may get A_p@;Pl%e2
the cleaned-up field should then contain ApPle2.
I know it can be done simply with a java function, but before we go
that route, I wanted to see if there was anyway to do it w/ SQL only..
Eventually, we will want to make a trigger, so as data is inserted into
the non clean field, the cleaned up field is automatically populated.
I have not yet found any DB2 functions which will do this, other then
doing a substring and case through each character, but that will make
some awefull looking SQL..
Any thoughts? Thanks
Regards,
Jay 6 6161
This also check all characters one by one. But, it is a little easier
to check each character.
CREATE FUNCTION TO_ALPHANUMp (inStr VARCHAR(4000))
RETURNS VARCHAR(4000)
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE non_an VARCHAR(4000);
DECLARE anStr VARCHAR(4000);
DECLARE slen, seq INTEGER;
SET non_an =
TRANSLATE(inStr ,'','abcdefghij klmnopqrstuvwxy zABCDEFGHIJKLMN OPQRSTUVWXYZ012 3456789');
SET slen = LENGTH(inStr);
SET seq = 1;
SET anStr = '';
WHILE seq <= slen DO
IF SUBSTR(non_an, seq, 1) = ' ' THEN
SET anStr = anStr || SUBSTR(inStr, seq, 1);
END IF;
SET seq = seq + 1;
END WHILE;
RETURN anStr;
END@
Perhaps loop through a FUNCTION that checks each digit?
CREATE FUNCTION AlphaNumeric(Di git VARCHAR(1))
RETURNS VARCHAR(1)
SPECIFIC AlphaNumeric
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
CASE
WHEN ASCII(Digit) BETWEEN 48 AND 57
OR ASCII(Digit) BETWEEN 65 AND 90
OR ASCII(Digit) BETWEEN 97 AND 122 THEN Digit
ELSE ''
END
B.
You can check it letter by letter. As Tonkuma said with TRANSLATE, or
use BETWEEN on the ASCII() value.
CREATE FUNCTION Is_AlphaNumeric (Digit VARCHAR(1))
RETURNS INTEGER
SPECIFIC Is_AlphaNumeric
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
CASE
WHEN ASCII(Digit) BETWEEN 48 AND 57
OR ASCII(Digit) BETWEEN 65 AND 90
OR ASCII(Digit) BETWEEN 97 AND 122 THEN 1
ELSE 0
END
And use it on each character.
B.
many thanks for the suggestions, i will look into them.. database is
utf-8, so i will have to check w/ requirements how to handle non-ascii
characters...
ok, so i got a little more info, and it looks like support for
non-ascii characters will be required, similar to the java
isLetterOrDigit method... have not seen something like that in db2
yet.. my search continues.. thx for the help
Jason Knaster wrote: ok, so i got a little more info, and it looks like support for non-ascii characters will be required, similar to the java isLetterOrDigit method... have not seen something like that in db2 yet.. my search continues.. thx for the help
You can wrap this Java method into an DB2 UDF.
Won't break any speed records, but I hope you don't do this a billion
times :-)
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: pilar |
last post by:
Hi All,
How can I validate the entry in a text field depending on the first
letter of the string. I need to have a text box to acept 14 characters
if the first letter entered is M and 17 characters if the first letter
of the entry is A. I have been trying to do it for days. I also tried
to look for any other similar issues in the newsgroup, access help,
microsoft knowledge base...could not find any help. I'll appreciatte
any...
|
by: Ron |
last post by:
How can I select a certain number of characters from a string, without
selecting the whole string?
For example, I want the first 50 characters of a 500 character string in my
database. What would the SQL command to do this look like?
Thanks,
Ron
|
by: darrel |
last post by:
I can grab a random number in vb.net like this:
Dim RandomClass As New Random
Dim RandomNumber As Integer
RandomNumber = RandomClass.Next(1, 26)
However, what I want is a random number. Short of making a case statement
with 26 options, is there a more streamlined way to get an integer between
1-26 translated into one of the letters?
|
by: =?Utf-8?B?UmljaA==?= |
last post by:
Greetings,
I have to load 30,000 unique names into a combox. Filling a dataTable takes
only a few milliseconds. But populating the combobox and displaying the list
takes several seconds - way too long. A user selects a name from the
combobox and runs a query.
Originally, this combox was on a form in an MS Access ADP which was linked
directly to our sql server. The combobox populated withins millisecnods.
How can I achieve this...
|
by: aa |
last post by:
when I ourput data from Access, numbers bigger than 999 are shown with
letter "B" inserted into the 4th position left to the coma. (in my w2k
regional settings I have the decimal dot separator set to "." and digit
group separater set to nothing. In Access the number are shown with "." as
decimal separator, but for some reason ASP used "," instead)
Say, 3000,00 is shown as 3B 000,00
Why and how do I sort this out?
| |
by: fniles |
last post by:
In VB.NET 2005 can I check if a letter in a string is upper case or lower
case ?
For example:
I have the following 2 lines:
NQ,Z2003,11/11/2003,1416.5,1420,1402,1411.5
NQ,z2003,11/10/2003,223801,260154
NQ,H2004,11/11/2003,1416.5,1422.5,1406.5,1415
NQ,h2004,11/10/2003,56,1191
Notice the 1st line has uppercase Z (Z2003) and the 2nd line has lowercase z
|
by: gazz |
last post by:
Hi,
Please could someone shed some light on this as I am really struggling to understand how to do this.
Given a word entered into a window.prompt EG "forum", how do I create a script to determine the postion of a letter by entering a number no longer than the string and output that letter to screen.
I have completed the prompting and selection the bold section below is where I become stuck. Any help would be appreciated greatly.
EG
...
|
by: Ulterior |
last post by:
Hi, everyone,
I have a simple problem, which bothers me for some while. I will try
to explain it -
There is some string, whith different letters in it. Is it possible to
analyse this string and the
reffer to it knowing that it has some certain letters in it using only
some integer value?
|
by: cloh |
last post by:
Hey all, I have a table in Access with 13 rows and 2 columns. The first column has numbers 1 to 13 in ascending order, and the second column has three-letter codes that correspond to each number, like so:
ID_number Code
1 ARS
2 CAD
In VB, I have a loop that generates a bunch of Excel files, so my loop goes for i = 1 to 13. I would like to be able to name the worksheet in the...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |