[CLOSED] SQL Code conversion in Focus

Can this function be written in FOcus?
ALTER FUNCTION [dbo].[GetPhoneClean]
(
@strAlphaNumeric VARCHAR(256)
)
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT

SET @intAlpha = Patindex('%[^0-9]%', @strAlphaNumeric)

BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = Stuff(@strAlphaNumeric, @intAlpha, 1, '')
SET @intAlpha = Patindex('%[^0-9]%', @strAlphaNumeric)
END
END

RETURN SUBSTRING(Isnull(@strAlphaNumeric, 0),1,10)
END

This function gets first 10 numeric charecters of from phone number filed. How can I accomplish this in webfocus.

My phone number field has some bad data and I want to show it in a standard format only 10 digit phone number and remove ext, person name etc.

This message has been edited. Last edited by: Kerry,

WebFOCUS 7.6.10
Windows
HTML

Posts: 294 | Registered: March 04, 2010

njsden

Virtuoso

posted

Hide Post

Though a similar functionality can be implemented in WebFOCUS, it won't necessarily be "elegant".

Wouldn't it be better to just cleanse the values in your database -either permanently in a new field or exposed as a calculated value in a view- and use that in your report?

As you already have a stored function that does that in your database it should be relatively easy to reuse in a view, shouldn't it?

This message has been edited. Last edited by: njsden,

Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.

Posts: 1533 | Registered: August 12, 2005

Ignored post by njsden posted

Show Post

Francis Mariani

Expert

posted

Hide Post

There does not appear to be any way to strip undesired characters from a string, so all I could come up with is a process to verify each character in the string - keep if numeric, reject if not.

This is an example of a "DEFINE FUNCTION" - a method of creating reusable code that can be called by Dialogue Manager or reporting. It uses the SUBSTR function to isolate one character at a time and then CHKFMT to check the format of the character. If it's numeric, keep, if it's not, reject. (I use the -REPEAT Dialogue Manager command to avoid writing 20 lines of code).

First, create the function:

DEFINE FUNCTION EXTRACTNUMS(INSTRING/A20)
-REPEAT ENDREP FOR &CTR FROM 1 TO 20
S&CTR/A1 = IF CHKFMT(1, SUBSTR(20, INSTRING, &CTR, &CTR, 1, 'A1'), '9', 'I1') EQ 0 THEN SUBSTR(20, INSTRING, &CTR, &CTR, 1, 'A1') ELSE '';
-ENDREP
EXTRACTNUMS/A20 = S1||S2||S3||S4||S5||S6||S7||S8||S9||S10||S11||S12||S13||S14||S15||S16||S17||S18||S19||S20;

Then call the function:

-SET &INPUTPHONE = 'F85T27E515-D3T87';
-SET &GOODPHONE = EXTRACTNUMS(&INPUTPHONE);

-TYPE BEFORE: &INPUTPHONE / AFTER: &GOODPHONE

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Show Post

njsden

Virtuoso

posted

Hide Post

Now, that's actually elegant Francis!

All there is for ABT to do is to get the first 10 characters from the final result which a simple EDIT(EXTRACTNUMS(PHONE), '9999999999') will do.

Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.

Posts: 1533 | Registered: August 12, 2005

Ignored post by njsden posted

Show Post

Francis Mariani

Expert

posted

Hide Post

I can improve this slightly by making the input string variable in length, but I would have to rely on a Dialogue Manager variable. I could also change this to extract either the alpha characters or the numeric characters...

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Show Post

Arif

Guru

posted

Hide Post

Francis and Njsden thank you so much for you help. It will work in my environment. I am going to test it today.

The reason I can not implement cleanup in my DB is because on the front end it is free form entry at the moment and to change the free form entry or impacting DB can create a new project for me.

Arif

WebFOCUS 7.6.10
Windows
HTML

Posts: 294 | Registered: March 04, 2010

Ignored post by Arif posted

Show Post

Please Wait. Your request is being processed...

Read-Only Topic

Focal Point

Focal Point Forums

WebFOCUS/FOCUS Forum on Focal Point

[CLOSED] SQL Code conversion in Focus

Focal Point | Privacy Statement | Terms of Service