• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • SQL Functions for Mashing Characters

    August 14, 2002 Timothy Prickett Morgan

    Hey, Howard:

    I have a field that has embedded ‘-‘ characters that I want to remove.

    For example, if the field contains 01-111-3345, I want to return it as 001113345.

    However, the translate function does not accept an empty string.

    Can you tell me how to remove characters from a string in SQL?

    — Mike

    Unfortunately, there is not an easy way to do this when you have more than one occurrence of the character in a string.

    However, a user-defined function using the Persistent Stored Modules (PSM), feature of DB2 is one way to get this done:

    CREATE function sqlbook.ELIMCHAR
    	(InString varchar(100),RepChar VARCHAR(1))
    	returns varchar(100)
    	LANGUAGE SQL
    BEGIN
    DECLARE OutStr varchar(100);
    DECLARE Spos INTEGER;
    SET OutStr=InString;
    SET spos = -1;
    WHILE spos<>0 DO
     SET Spos = locate(RepChar,OutStr,1); 
     IF Spos>1 AND spos<LENGTH(outstr)-1 THEN
      SET OutStr = substr(OutStr,1,Spos-1)||
          substr(OutStr,Spos+1,LENGTH(OutStr)-spos);
     ELSEIF Spos=LENGTH(outstr) THEN
      SET OutStr = substr(OutStr,1,spos-1);
     ELSEIF spos=1 THEN
      SET OutStr = substr(OutStr,2,LENGTH(outstr)-1);		
     END IF;
    END WHILE;
    RETURN OutStr;
    END
    

    This user-defined function uses the LOCATE function to find the occurrence of the string. If LOCATE returns 0, none of the conditions are executed and the loop exits. If LOCATE returns a position, first I determine if the position is greater than (>) one and less than (<) the length of the string, which indicates the character is somewhere in the middle of the string. If so, I use the returned position of the character as an argument to the substring function, which returns beginning and ending strings. Subsequently, the CONCAT operator (||) is used to meld the substrings together.

    If the position of the passed character is at the end of the string, I use the SUBSTR function to return the string minus the ending character. Finally, if the string is in the first position, I use SUBSTR to remove just the first character. The function loops until all occurrences of the character are replaced.

    Here’s one thing to note about this function: I used the VARCHAR data type for both the input string, (InString), the matching character, (RepString), and the return data type, (OutStr). This is because all CHAR data types can be promoted to VARCHAR automatically by SQL, but VARCHAR data types cannot be demoted to CHAR. Any strings you pass to the function will be seen as VARCHAR, so if the arguments are declared as CHAR, the function will not be found. Remember, SQL looks for the user-defined functions not only by name, but also ensures that the data types for the arguments match or are compatible.

    Here’s an example of how this could affect you. Say I coded the function in the following manner:

    CREATE function sqlbook.ELIMSTRING
    	(InString varchar(100), RepChar CHAR(1))
    	returns varchar(100)
    ...... more function stuff
    

    If I have a table called XYZ that has a column called ABCD, which is type CHAR(20), the following call will NOT work:

    SELECT ELIMSTRING(ABCD,'-') FROM XYZ
    

    This call will fail and indicate that the function ELIMSTRING is not found. It does not fail because column ABCD is a CHAR(20) and the function is coded to accept only VARCHAR(100) arguments in the first position. It will fail because the second argument, ‘-‘, is seen by DB2 as a VARCHAR data type and the function is declared to accept only CHAR(1) data types in the second argument. However, if I code the function with the following declaration it will work:

    CREATE function sqlbook.ELIMSTRING
    	(InString varchar(100), RepChar VARCHAR(1))
    	returns varchar(100)
    ...... more function stuff
    

    DB2 knows that a CHAR(20) column is compatible with a VARCHAR(100) column, so that passes the data-type test. Since DB2 sees the second argument as a VARCHAR(1) anyway, the second argument also passes the test and the proper function is “found”. If you think about it, it is kind of stupid to declare an argument as VARCHAR(1), but this is required if you want to pass your arguments as literals because DB2 will see the literal string as a VARCHAR, not a CHAR of the length of the literal.

    I hope this helps.

    — Howard

    Howard F. Arner, Jr. is Vice President of Client Server Development, Inc., in Jacksonville, Florida, and he is the author of “iSeries and AS/400 SQL at Work.” You can buy Howard’s book at www.sqlthing.com/books, or go there to get more information about DB2 on the AS/400.

    Sponsored By
    COMMON

    REGISTER FOR COMMON IN DENVER, OCT. 13-17

    Get the IT training you need by attending COMMON Users Group’s Fall 2002 IT Education Conference & Expo, October 13-17 in Denver. Early Bird registration is $1,150 until September 4.

    Choose from over 720 sessions and labs covering a wide range of industry topics. Also receive training from J.D. Edwards, MAPICS, and other vendors.

    Don’t miss out! Go to www.common.org

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: mgo_rc, Volume 2, Number 61 -- August 14, 2002

    Sponsored by
    PERFSCAN

    Revolutionary Performance Management Software

    At Greymine, we recognize there is a void in the IT world for a dedicated performance management company and also for a performance management tool that’s modern, easy to use, and doesn’t cost an arm and a leg. That’s why we created PERFSCAN.

    PERFSCAN is designed to make your job easier. With revolutionary technology, an easy-to-read report and graphics engine, and real time monitoring, tasks that used to take days can now take minutes. This means you will know your system better and will be able to provide better service to your customers.

    OUR FEATURES

    PERFSCAN is full of robust features that don’t require you to take a three-day class in order to use the product effectively.

    Customizable Performance Reporting

    Whether you are troubleshooting a major system problem or simply creating a monthly report, PERFSCAN lets you select any combination of desired performance metrics (CPU, Disk, and Memory).

    User Defined Performance Guidelines

    No matter if you are a managed service provider managing complex systems in the cloud or a customer analyzing your on-premises solution, PERFSCAN gives you the flexibility to define all mission critical guidelines how they need to be.

    Understanding The Impact Of Change

    Tired of all the finger pointing when performance is suffering? PERFSCAN’s innovative What’s Changed and Period vs. Period analysis creates a culture of proof by correlating known environmental changes with system performance metrics.

    Comprehensive Executive Summary

    Creating performance graphs is easy. Understanding what they mean is another thing. With one mouse click, PERFSCAN includes an easy-to-understand executive summary for each core metric analyzed.

    Combined Real-Time Monitor And Performance Analysis Tool

    With PERFSCAN’s combined built in enterprise real-time monitor and historical performance analysis capability, you will always know how your mission-critical systems are performing.

    Cloud Performance Reporting Is Easy

    Managing performance for production systems in the cloud can be a black hole to many system administrators. The good news is PERFSCAN analyzes all core metrics regardless of the location. That’s why MSPs and customers love PERFSCAN.

    Detailed Job Analysis

    PERFSCAN shows detailed top job analysis for any desired period. All metrics are displayed in two ways: Traditional Report and Percentage Breakdown Pie Chart. This toggle capability instantly shows the jobs using the most system resources.

    Save Report Capability

    Your boss lost the report you gave to him on Friday. Now what do you do? With PERFSCAN’s save report capability, any report can be retrieved in a matter of seconds.

    Professional PDF Reporting With Branding

    Creating professional looking reports for your customers has never been easier with PERFSCAN. Branding for our partners and service provider customers is easy with PERFSCAN.

    Check it out at perfscan.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Date and Time Functions in V5R1 RPG Confirm File Deletions in Qshell

    Leave a Reply Cancel reply

MGO Volume: 2 Issue: 61

This Issue Sponsored By

    Table of Contents

    • Using Timestamps as Unique Keys
    • Make an SQL UDF Return Null
    • SQL Functions for Mashing Characters

    Content archive

    • The Four Hundred
    • Four Hundred Stuff
    • Four Hundred Guru

    Recent Posts

    • Fortra Issues 20th State of IBM i Security Report
    • FNTS Launches Managed Services for Power Servers in IBM Cloud
    • Total LTO Shipped Capacity Up Slightly in 2022
    • Four Hundred Monitor, May 24
    • Update On Critical Security Vulnerability In PowerVM
    • Critical Security Vulnerability In PowerVM Hypervisor
    • IBM Power: Hosted On-Premises Or In The Cloud?
    • Guru: Watch Out For This Pitfall When Working With Integer Columns
    • As I See It: Bob-the-Bot
    • IBM i PTF Guide, Volume 25, Number 21

    Subscribe

    To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

    Pages

    • About Us
    • Contact
    • Contributors
    • Four Hundred Monitor
    • IBM i PTF Guide
    • Media Kit
    • Subscribe

    Search

    Copyright © 2023 IT Jungle