0

I have a table TableA with a tilde operated column ColumnA

TableA 

**ColumnA**  
123~abc~def~~~~~ghi~j~k~lmn~op~~~  
231~a~dfg~wer~~~~~~~hijkl~~~

As we can see in the above two rows, it is '~' separated. I basically want to separate the values into individual columns. There are 15 '~' operators.

My output table should be something like 

Col1        Col2       Col3     Col4 .   . .. . . .. .. .. .. .  
123          abc       def             . .. . .. .. ... .. . . .

I have a query in DB2 which will do this but it requires 15 subqueries to achieve this task as there are 15 '~' operators. Given below: 

SELECT substr(ColumnA, 1, LOCATE('~', ColumnA)-1) AS Col1, 
  substr(ColumnA, charindex('~', ColumnA)+1, LEN(ColumnA)) AS Other
FROM TableA

I am separating Col1 only by the above query. If I wish to separate 15 columns, I will have to subquery this 15 times.

Is there a better way to do this?

Thank you

AngocA
  • 7,655
  • 6
  • 39
  • 55
Rikin
  • 283
  • 3
  • 13
  • 29
  • 1
    You may have to write your own version of [split function](http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x). This is a SQL Server version. – user2989408 Aug 07 '14 at 17:55
  • Er, DB2 has a bulk-load utility that can parse delimited files (like pretty much all RDBMSs). Maybe output to a file and reload with the utility, supplying `~` as the delimiter? If the original `ColumnA` is from a bulk-load anyways, change your process to load to a separated format. If not... it's usually a terrible idea to store delimited fields in the first place, not to mention fields that appear to contain different things... – Clockwork-Muse Aug 08 '14 at 10:11

1 Answers1

2

Someone was kind enough to write this split function for DB2

http://www.mcpressonline.com/sql/techtip-create-an-sql-function-to-split-a-delimited-list.html

CREATE FUNCTION QGPL.SPLIT (
@Data     VARCHAR(32000),
@Delimiter VARCHAR(5))

RETURNS TABLE (
ID   INT,
VALUE VARCHAR(256))

LANGUAGE SQL
DISALLOW PARALLEL
DETERMINISTIC
NOT FENCED

RETURN
WITH CTE_Items (ID,StartString,StopString) AS
(
   SELECT
       1 AS ID
       ,1 AS StartString
       ,LOCATE(@Delimiter, @Data) AS StopString
   FROM SYSIBM.SYSDUMMY1
   WHERE LENGTH(@Delimiter)>0
     AND LENGTH(@Data)>0

UNION ALL

SELECT
   ID + 1
   ,StopString + LENGTH(@Delimiter)
   ,LOCATE(@Delimiter, @Data, StopString + LENGTH(@Delimiter))
FROM
   CTE_Items
WHERE
   StopString > 0
)
SELECT ID, SUBSTRING(@Data,StartString,
   CASE WHEN StopString=0
       THEN LENGTH(@Data)
       ELSE StopString-StartString END)
 FROM CTE_Items;
bowlturner
  • 1,968
  • 4
  • 23
  • 35
  • How does the above function work? Will it split all 15 '~' operated columns into its individual columns. I do not have create function or create procedure access rights. Is it possible to create a view or use a select statement? – Rikin Aug 07 '14 at 18:31
  • Since your values are in a table, it is possible to use some of this to create a view on that table I would think. – bowlturner Aug 07 '14 at 18:33
  • can I achieve the above sql using select and not a function since I do not have function or procedure rights? – Rikin Aug 07 '14 at 19:46
  • if you can't write a function, procedure or view (and no one else can or has for you) then you'll be best off continuing to write the query as you were, detail each column out. Use copy paste and once it's done once you can reuse it as well... – bowlturner Aug 07 '14 at 19:52
  • Sorry couldn't be more help – bowlturner Aug 07 '14 at 20:47
  • The one problem with this function alone is that it returns (in this case) 15 _rows_, but the OP will need it as columns. You need one more step (pivoting) to be able to use this. Recursive CTE splits are common though - you can certainly use the interior of the function as part of a normal statement, yes. – Clockwork-Muse Aug 08 '14 at 08:35