SQL Scalar Functions POSSTR, LOCATE, LOCATE_IN_STRING

0
1486

I this post we will take a look to some SQL Scalar Functions (DB2 SQL for i) … let’s get started with a temporary table and some example data …

CREATE  TABLE QTEMP/NEWTAB (ID char(10), MYFIELD01 char(100))
INSERT INTO QTEMP/NEWTAB (ID, MYFIELD01) VALUES('00001', 'Paolo,Rossi')
INSERT INTO QTEMP/NEWTAB (ID, MYFIELD01) VALUES('00002', 'Marco,Bianchi')
INSERT INTO QTEMP/NEWTAB (ID, MYFIELD01) VALUES('00003', 'Giuseppe,Verdi')

Now we can see how to use POSSTR to get comma position and split string name and surname.ù

SELECT id, substr(myfield01, 1, posstr(myfield01, ',')-1) AS NAME,
substr(myfield01, posstr(myfield01, ',')+1) AS SURNAME,
myfield01
FROM qtemp/newtab

This is what we have …

ID        NAME        SURNAME   MYFIELD01
00001 Paolo         Rossi               Paolo,Rossi
00002 Marco       Bianchi           Marco,Bianchi
00003 Giuseppe Verdi                Giuseppe,Verdi

We can also use LOCATE function with small change to our sql query :

SELECT id, substr(myfield01, 1, locate(',', myfield01)-1) AS NAME,
substr(myfield01, locate(',', myfield01)+1) AS SURNAME,
myfield01
FROM qtemp/newtab

With last TR for IBMi 7.1 and 7.2 we can use also LOCATE_IN_STRING scalar function … take a look a this URL: <BR>

https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/LOCATE_IN_STRING%28%29%20built-in%20function

Se you soon !