Tag Archives for " #SQL "

Oct 31

IBM i SQL – Using SUBST to insert data into a string

A while ago I had a problem where I wanted to change the first two characters in a table and I used the very simple concat and subst operations to do it with SQL. Today, we had a slightly more complex requirement of updating text in the middle of a field using SQL, or in […]

More Waffle
Jun 13

Funky IBM i Email Validation Program using SQL Regex

Following on from my previous blog (basic email validation) I decided that it would much nicer to (a) have the regex stored in a data area so that we can easily play with it to try different validation scenarios and (b) lets have a shot at a much stricter validation because there is an official […]

More Waffle
Jun 13

Simple email validation SQL RPG ILE program

There are lots of code snippets, example programs and long waffling discussions in internet land filled with pointy headed AS400, iSeries and IBM i programmer chaps (and chappettes) arguing about the best way to “validate an email address in RPG”. I know because I’ve read nearly all of them, as well as tried all the […]

More Waffle
May 12

Use IBM i SQL to validate email addresses in Customer Master File

So, we have a customer masterfile that’s been around for years. Users have been keying in customer info and sometimes adding email addresses, sometimes not, sometimes entering nonsense or even just text that says things like “this chap has no email so don’t email him”. Yes, it’s a filthy dirty file filled with grubby email […]

More Waffle
Sep 13

Varying Length fields in DDS and IBM i SQL

Back in the olden days – files were filled with numbers and long string of data. Words like SAUSAGES might have been stored as a 30 ALPHA field in the format “SAUSAGES______________________” which, even tho its padded with *blanks – is just wasting a lot of space. Nowadays, most systems use variable length fields, shorter, […]

More Waffle
Aug 23

How to change a Files alternative name using IBM i SQL

SQL works very nicely on IBM i Servers — the added benefit is that we can access the database using SQL from other machines, access it using IBM SQL and also using Native (DB2) File IO. But, SQL has some features that native database IO does not support – huge long table names (aka “alternative […]

More Waffle
Aug 17

Scan Replace in RPGLE and SQLRPGLE

In the middle of this RPG program, I want to reformat and replace some values in a big long address field. But for the life of me… I cant remember how to use the SCANREPLACE built in function in RPG. #Dingbat /me memory circuits need a service methinks. So, like all programmers, I wonder off to […]

More Waffle
1 2 3 4