Split a Varchar in Db2 to Retrieve a Value Inside

Split a VARCHAR in DB2 to retrieve a value inside

CREATE FUNCTION split(pos INT, delimeter CHAR, string VARCHAR(255))
LANGUAGE SQL
RETURNS VARCHAR(255)
DETERMINISTIC NO EXTERNAL ACTION
BEGIN ATOMIC
    DECLARE x INT;
    DECLARE s INT;
    DECLARE e INT;

    SET x = 0;
    SET s = 0;
    SET e = 0;

    WHILE (x < pos) DO
        SET s = locate(delimeter, string, s + 1);
        IF s = 0 THEN
            RETURN NULL;
        END IF;
        SET x = x + 1;
    END WHILE;

    SET e = locate(delimeter, string, s + 1);
    IF s >= e THEN
        SET e = LENGTH(string) + 1;
    END IF;
    RETURN SUBSTR(string, s + 1, e - s -1);
END!

Usage:

SELECT split(3,'$',col) from mytable; -- or
SELECT split(0,'-', 'first-second-third') from sysibm.sysdummy1;
SELECT split(0,'-', 'returns this') from sysibm.sysdummy1;
SELECT split(1,'-', 'returns null') from sysibm.sysdummy1;

How to split a string value based on a delimiter in DB2

This is what i tried and it fetched me effective result. Hence sharing with all.

select column_name, substr(column_name,1,locate('-',column_name)-1), 
substr(column_name,locate('-',column_name)+1,
length(substr(column_name,locate('-',column_name)+1))) from 
table_name where column_name is not null and column_name!='' 
and column_name like '%-%'

How to split string in based on a certain delimiter in DB2 without creating stored procedure

I don't have access to DB2 to check it but it should work or will be very easy to adapt:

CREATE TABLE PersonTable(Person_Info VARCHAR(1000));

INSERT INTO PersonTable(Person_Info)
SELECT 'Person_BILL_1234_1511011900'  union all
SELECT 'Person_BOB_88888'  union all
SELECT 'Person_MARIOSAN_10_1511011900';

Query:

SELECT LEFT(sub.r, LOCATE('_', CONCAT(sub.r, '_'))-1) AS result
FROM (
  SELECT RIGHT(Person_Info, LENGTH(Person_Info) - 
                            LOCATE( '_',Person_Info, LOCATE('_', Person_Info)+1)) AS r
  FROM PersonTable
  ) AS sub

SqlFiddleDemo

How it works:

1. In subquery I get the right starting from second _ character
2. In main query I get left part to next _ character (added CONCAT to make sure it exists
3. You can cas result to INT if needed using DB2 syntax

Assuming that you have always Person_ at the beginning:

SELECT Person_Info,
 LEFT(RIGHT(Person_Info, LENGTH(Person_Info) - LOCATE('_', Person_Info, 8)),
            LOCATE('_', CONCAT(RIGHT(Person_Info, LENGTH(Person_Info) 
                                                - LOCATE('_', Person_Info, 8))
                        ,'_')
                  )-1)
           AS result
FROM PersonTable

SqlFiddleDemo2

Output:

╔════════════════════════════════╦════════╗
║          Person_Info           ║ result ║
╠════════════════════════════════╬════════╣
║ Person_BILL_1234_1511011900    ║   1234 ║
║ Person_BOB_88888               ║  88888 ║
║ Person_MARIOSAN_10_1511011900  ║     10 ║
╚════════════════════════════════╩════════╝

How to extract a value from a delimited string in Db2

Try this for table BILL and its column COL1 with data.

SELECT 
  COL1
-- since 9.7
, xmlcast(xmlquery('fn:tokenize($s, "\|")[3]' passing BILL.COL1 as "s") as varchar(20)) as one
-- since 11.1
, REGEXP_SUBSTR(BILL.COL1 || '|', '([^\|]*)\|', 1, 3, '', 1) as two
FROM 
(VALUES '0410|M|PAXG|20181114', '0410|M||20181114') BILL (COL1)
--BILL
;

How to split a string after specific character in SQL Server and update this value to specific column

Try this:

UPDATE YourTable
SET Col2 = RIGHT(Col1,LEN(Col1)-CHARINDEX('/',Col1))

String split column and join to another table

You can greatly simplify this to something like this.

SELECT
    O.[ID]                  AS OfferID,
    O.[Name]                AS OfferName,
    c.[CategoryName]      AS CategoryName,
    c.[CategoryID]        AS CategoryID
FROM
    JobOffers AS O
outer apply [dbo].[Split](O.[Categories], ',') s
left join Categories as C on c.CategoryID = s.Items

The concern I have is your splitter. If there is more than a single select statement the performance is going to suffer horribly. For a good explanation of various splitters available you can visit this article.

http://sqlperformance.com/2012/07/t-sql-queries/split-strings

SQL split values to multiple rows

If you can create a numbers table, that contains numbers from 1 to the maximum fields to split, you could use a solution like this:

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from
  numbers inner join tablename
  on CHAR_LENGTH(tablename.name)
     -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
order by
  id, n

Please see fiddle here.

If you cannot create a table, then a solution can be this:

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from
  (select 1 n union all
   select 2 union all select 3 union all
   select 4 union all select 5) numbers INNER JOIN tablename
  on CHAR_LENGTH(tablename.name)
     -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
order by
  id, n

an example fiddle is here.

---

Related Topics