473,554 Members | 3,228 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Help w/ stored proc creation - extract only numeric from alphanumeric

aj
DB2 WSE 8.1 FP 5
Red Hat Linux AS 2.1
(LUW)

I am looking to write a stored procedure
GETNUMBER(p_inp ut VARCHAR(50)) RETURNS Integer
that will return to me only the numeric portion of
alphanumeric p_input, dropping the alpha portion(s).

So,
GETNUMBER('B999 ') would return 999
GETNUMBER('B888 A') would return 888
GETNUMBER('ABCD EFG123') would return 123
GETNUMBER('1234 5YXEWQ') would return 12345

You get the idea.....

I have this written as a Java UDF, but have had nothing but
headaches w/ v8 FP5 Java VM FMP issues.

Can this be written as a stored procedure?

Any help appreciated.

aj

Nov 12 '05 #1
2 2646
Ian
aj wrote:
DB2 WSE 8.1 FP 5
Red Hat Linux AS 2.1
(LUW)

I am looking to write a stored procedure
GETNUMBER(p_inp ut VARCHAR(50)) RETURNS Integer
that will return to me only the numeric portion of
alphanumeric p_input, dropping the alpha portion(s).

So,
GETNUMBER('B999 ') would return 999
GETNUMBER('B888 A') would return 888
GETNUMBER('ABCD EFG123') would return 123
GETNUMBER('1234 5YXEWQ') would return 12345


TRANSLATE can help, although it's not so easy without character classes
(since you basically have to specify every character except for 0-9.

INT(TRANSLATE(' B999','','AaBbC cDdEeFfGgHhIiJj KkLlMmNnOoPpQqR rSsTtUuVvWwXxYy Zz'))
----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---
Nov 12 '05 #2
Why do you want to implement as stored procedure?
It's easy to implement as SQL UDF.
For example:
CREATE FUNCTION GETNUMBER(p_inp ut VARCHAR(50))
RETURNS Integer
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
NO EXTERNAL ACTION
RETURN
CAST(REPLACE(TR ANSLATE(p_input , '', 'ABCDEFGHIJKLMN OPQRSTUVWXYZ'),
' ', '') AS INTEGER)
!
Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
28050
by: June Moore | last post by:
Hi all, I have a stored procedure that return a resultset e.g. stored proc: get_employee_details select emp_id, emp_name, emp_salary, emp_position from empoloyee I would like to write another stored procedure that executes the above stored procedure - returning the same number of records but it will only show 2 columns
11
10309
by: shan | last post by:
how to declare a variable that should contain both alpahabets and numbers.It should not be an string or char array.
6
4967
by: James Radke | last post by:
Hello, I have a multithreaded windows NT service application (vb.net 2003) that I am working on (my first one), which reads a message queue and creates multiple threads to perform the processing for long running reports. When the processing is complete it uses crystal reports to load a template file, populate it, and then export it to a...
45
3374
by: John | last post by:
Hi When developing vb.bet winform apps bound to sql server datasource, is it preferable to use SELECTs or stored procedure to read and write data from/to SQL Server? Why? Thanks Regards
1
3697
by: Rahul | last post by:
Hi Everybody I have some problem in my script. please help me. This is script file. I have one *.inq file. I want run this script in XML files. But this script errors shows . If u want i am attach this script files and inq files. I cant understand this error. Please suggest me. You can talk with my yahoo id b_sahoo1@yahoo.com. Now i am...
3
6861
by: comp_databases_ms-sqlserver | last post by:
This post is related to SQL server 2000 and SQL Server 2005 all editions. Many of my stored procedures create temporary tables in the code. I want to find a way to find the query plan for these procs Repro --*********************************** use pubs go CREATE PROCEDURE Test @percentage int
4
2051
by: raghav | last post by:
Hi all I am having a SP which is returning a value......Now I have to store that value in session...I saw some examples in msdn lib ----> string Name=string.Empty; Session=Name.ToString(); But in my case return value is ID ...and it has not been declared in C#
3
6382
by: satkins | last post by:
Hello everyone. I've been trying to find if this is even possible but nothing I've found has really helped. I've got a stored proc with a few input parameters and one output parameter. I would like to call this proc from another proc and have proc 2 return the result of proc1 as a column. Some thing like this create procedure Proc1 ...
5
28537
lotus18
by: lotus18 | last post by:
Hello World! I have a sample code here written in vb .net that restricts the textbox to accept only alpha, alphanumeric or numeric characters. Public Enum MyOption Alpha = 1 AlphaNumeric = 2 Numeric = 3 End Enum
0
7570
marktang
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...
0
8008
jinu1996
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...
1
7526
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
1
5411
isladogs
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...
0
5133
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3525
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1992
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
1
1107
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
808
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.