SQL / Parse a String

c149187 · #1 (**permalink**) 04-01-04, 07:47

Hi

I have 1 database column PERSONDETAIL which includes in 1 field different attribut values seperated by ;

Example:
Muller;Andrew;Greenstreet 3;8356;Chicago; ;052 789 99 99;x.y@gmx.ch; ; ; ;

How can I get the different values:
Muller
Andrew
Greenstreet
...

Can I build a function like getAttributvalue (PERSONDETAIL, 3)
which returns the third value, in the example it would be Greenstreet

Thank, Marc

J Petruk · #2 (**permalink**) 04-01-04, 09:28

You could probably use some combination of LOCATE() and SUBSTR() to do what you want. LOCATE the position of the ";" then use SUBSTR. Might needs some recursion to get to the next ";".

J Petruk · #3 (**permalink**) 04-01-04, 09:41

This one is too fun to pass up...

Here's a start... it gets the 2nd element:

WITH TMP_TAB (shortstring)
AS ( SELECT SUBSTR(COL1, (LOCATE(';', COL1)+1)) from mytable)

select SUBSTR(shortstring, 1, locate(';', shortstring)-1) from tmp_tab;

Recursion could probably make this pretty slick, allowing you to get the 3rd, 4th, etc... good luck!

(Or you could just write a UDF that uses Java or some other language that lends itself to this sort of thing)

Damian Ibbotson · #4 (**permalink**) 04-01-04, 11:21

I think this might do it...

Code:

CREATE FUNCTION LOCATE_FIELD (DELIM CHAR(1), STRING VARCHAR(2000), FIELD_POS SMALLINT)
RETURNS VARCHAR(100)
LANGUAGE SQL
DETERMINISTIC
------------------------------------------------------------------
-- SQL function to return field from delimited string
------------------------------------------------------------------
RETURN
WITH CHAR_POSITIONS(INDEX, COUNTER) AS
(
    VALUES (0, 0)

    UNION ALL

    SELECT COALESCE(LOCATE(DELIM, STRING, INDEX + 1), 0)
    ,      COUNTER + 1
    FROM   CHAR_POSITIONS
    WHERE  LOCATE(DELIM, STRING, INDEX + 1) != 0
      AND  COUNTER < FIELD_POS - 1
)
VALUES (
        CASE WHEN (SELECT MAX(COUNTER) FROM CHAR_POSITIONS) < (FIELD_POS - 1)
          THEN CAST (NULL AS CHAR)
          ELSE SUBSTR ( STRING
                       ,(SELECT MAX(INDEX) FROM CHAR_POSITIONS) + 1
                       ,LOCATE( DELIM, STRING, (SELECT MAX(INDEX) FROM CHAR_POSITIONS) + 1  ) -
                        (SELECT MAX(INDEX) FROM CHAR_POSITIONS) - 1
                      )
        END
       )
;

I haven't been able to test the above code because (a) there's something wrong with the database (b) there's something wrong with the code or (c) i'm being really stupid. ( I get a SQLSTATE=42884 )

Conceptually, I reckon it's sound. The only proviso I envisage is that the input string should be terminated with the specified delimiter.

If anyone gets this working and can tell me where I've gone wrong I'd like to know. (UDFs are not my strong point!)

Damian

J Petruk · #5 (**permalink**) 04-01-04, 11:28

Nice work...

I did a quick test - only thing it doesn't handle as you said was that the last char of the string has to be the delimiter.

Internet Services

Web Development

Digital Marketing

Consumer Tech