DB2 for i SQL – String Manipulation – POSSTR-LOCATE-LOCATE_IN_STRING (EN)

Last Updated on 27 August 2019 by Roberto De Pedrini

Introduction

Often, in our applications, we need to work with text strings, and DB2 SQL can come in very useful and simplify our code. In this guide we see some interesting SQL functions for string manipulation:

• POSSTR – Search for position within a string
• LOCATE and LOCATE_IN_STRING – Find the location with a few more options

POSSTR – Search position in a string

This function returns the starting position of a character or string within another string, for example if I look for “only” within the string “This is only an example” I expect a “9” as a result… simple I would say!

select posstr('This is only an example', 'only')
from sysibm.sysdummy1;
-- Result 
9

In a concrete example, we could use the POSSTR function to “pars” a CSV:

-- (1) We simulate a table with a string like CSV
CREATE or replace TABLE 
     qtemp.my_csv_file
      (csvstring varchar(300));

--(2) Let's populate the table with some examples 
 INSERT INTO qtemp.my_csv_file (csvstring) 
    VALUES('Christine; Hass')
              , ('Micheal; Thompson')
              , ('Roberto;De Pedrini');

--(3) We read the normal content
SELECT csvstring from qtemp.my_csv_file;

-- Result
CSVSTRING file
Christine; Hass
Micheal; Thompson
Roberto;De Pedrini

;
--(4) We pars the CSV
 SELECT substr(csvstring, 1, POSSTR(csvstring, ';') -1) AS FirstName,
             substr(csvstring, POSSTR(csvstring, ';') (AS LastName
 FROM qtemp.my_csv_file;

-- Result
FIRSTNAME LASTNAME
Christine Hass
Micheal Thompson
Roberto De Pedrini

LOCATE, In 

When things get complicated and we have to look for one string inside another starting from a certain location LOCATE and LOCATE_IN_STRING are more useful than the POSSTR function just seen: but beware of the syntax of these scalar function… parameters are reversed this time compared to before.

Let’s start with a simple example … we look for the string “this” starting from position 10 of a second string:

select locate('this', 'this is only an example, but this time we can start everywhere', 10)
from sysibm.sysdummy1;
-- Result
30

Starting with the qTEMP table created in the previous step, this time we use the LOCATE function to achieve a similar result:

SELECT substr(csvstring, 1, locate(';', csvstring,1)-1) AS FirstName
,substr(csvstring, locate(';', csvstring, 1)'1) aS LastNam
eFROM qtemp.my_csv_file

;--Result
FIRSTNAME LASTNAME
Christine Hass
Micheal Thompson
Roberto De Pedrini

LOCATE_IN_STRING

When the going gets tough… LOCATE_IN_STRING starts playing!

You like to win easy with a CSV of just two items!  Let’s complicate our lives with a slightly more real example. attention… the parameters of the scalar funtion are reversed another time … thank you SQL for always making our life easy (“you kill them!’).

-- (1) Let's simulate a table with a string like C
SVCREATE or replace TAB
LE qtemp.my_csv_f
ile(csvstring varchar(3

00));--(2) Let's populate the table with some
 examples INSERT INTO qtemp.my_csv_file (
csvstring) VALUES('Christine; Ha
ss; A01; Press'),('Micheal; Thom
pson; A02; Manager'),('Roberto;De P

edrini; A03; Big Boss');--(3) Let'
s read the normal contentSELECT csvstr

ing from qte
mp.my_cs
v_file;-- ResultCSVSTRI
NGChristine; Hass; A01; Pre
ssMicheal; Thompson; A02; Mana

g
erRoberto;De Pedrini:A03; Big Bo
ss;--(4) We pars the CSVSELECT locate_in_string (csvs
tring, ';'1,1) as Primo, locate_in_string (csvs
tring, ';'1,2) as Second,locate_in_string (c

svstring, ';'1,3) as t


hirdFROM qtemp.my_csv_file; SELECT substr(csvstring, 1, locate_in_string(csvstr
ing, ';'1,1)-1) AS FirstName,substr(csvstring, locate_in_string(csvstring, ';',1,1)-1, locate_in_string (csvstring, ';'1,2)-locate_in_string (c
svstring, ';'1,1)-1) to LastName,substr ""csvstring", locate_in_string (csvstring, 'csvstring,'1,1,2)-1, locate_in_string (csvstring, '1,3)-locat
e_in_string (csvstring, ';',1,2)-1) aS Workdepth,substr(csvstr


ing, locate_in_string 

(csvstrin
g, ';'1,3)-1) aS JobFROM qtemp.my_csv_
file ResultFIRSTNAME LASTNAME WORKDEPTH 
JOBChristine Hass A01 PressMicheal Thompso
n A02 ManagerRoberto De Pedrini A03 Big Boss

Conclusion

Three simple scalar functions for string manipulation… but much more awaits us!

Related Posts

DB2 for i SQL – Stringhe – POSSTR-LOCATE-LOCATE_IN_STRING (IT)

Introduzione Spesso, nelle nostre applicazioni, abbiamo la necessità di lavorare con le stringhe di testo e l'SQL del DB2 può Read more

DB2 for i – FAQ & Howtos (EN)

DB2 Database and SQL ... maybe the most important things on IBM i platform: here's a collection of FAQs, tips Read more

IBM i 7.4 Announcement (En)

Comes directly with the Easter egg this IBM announcement for the news of the IBM i 7.4 version, iNext version Read more

Generated Always Columns (EN)

Introduction "Generated Always Column": are columns, table fields, filled by DB2 engine: something like columns with a default value but Read more