0

First of all, hi everyone, I hope you're having a great day. So here's my problem. I have a column in my table that consist of values all append together like

1120304050607080

I need to get this value and then split it into many 2 digit values so i can use them in another query like

WHERE model in ('11,20,30,40,50,60,70,80') ...

Is this possible to do in a Db2 iSeries Navigator query ? If not, is there another way around ?

Thanks Have a great day !

EDIT: Seems like the solution will be to do a function. I haven't done a function in iseries before so if anyone is willing to help, i'll take it !

Kaptin
  • 41
  • 7
  • What about this http://stackoverflow.com/questions/18961996/how-to-split-a-string-value-in-db2. – jsh Oct 06 '15 at 19:13
  • Hi, thanks for the reply but i do not have a mark symbol to tell me when to split, it must be done every 2 characters. This solution seems to work only if you have a marker like '-' in the case of the link you put ? Correct me if i'm wrong. – Kaptin Oct 06 '15 at 19:17
  • What version of the OS are you using? There are regular expressions in the latest releases of DB2 for i. – John Kuhns Oct 06 '15 at 19:47
  • Yes, I did notice that. I am looking at the repeat function to see if there is a way to avoid a loop. However, you may be stuck with a look if there isn't a built in function I can pass an expression of n+2 for length of the string. – jsh Oct 06 '15 at 19:53
  • @JohnKuhns : i got IBM System i Acces for Windows Version 7 Release 1 Modification Level 0. – Kaptin Oct 07 '15 at 11:48
  • @jsh : i did look at the scalar function link, but i do not see if any of these can be useful in my case. My only thought was if I could use SUBSTR for some "loop" like you said and it could extract first and second character together, then the third and fourth, etc. – Kaptin Oct 07 '15 at 12:07
  • Do all the rows have the same number of values? – Charles Oct 07 '15 at 12:25
  • Hi @Charles, it's a unique row. I need to get this particular row because it contains all "product numbers" (that's how we call them here). These numbers need to be in a WHERE condition to be able to get some other data. Getting the row is easy, but formatting it the way i need is the part i have no idea on how to do it. Hope this helps. – Kaptin Oct 07 '15 at 12:31
  • To resume my problem, i need to put some ',' between each pair of characters except the last one from a particular row in a table so i can use them in a where condition to get data from another table. – Kaptin Oct 07 '15 at 12:35
  • So you only have 1 row in which to split? – Charles Oct 07 '15 at 12:40
  • @Charles : Yes. I can get the value of the row, but it need to be formatted so i can use it in a "WHERE IN('')" condition with every value separated by a comma. – Kaptin Oct 07 '15 at 12:44
  • @Kaptin - You need _some_ sort of loop, there's no way around it. There isn't generally a `SPLIT` function in SQL because using it means you're (usually) doing something wrong. Outside of some very specific circumstances, you shouldn't be storing concatenated values like this. – Clockwork-Muse Oct 07 '15 at 12:56
  • @Clockwork-Muse : I wish it was done differently but it has been implanted like this way before i was even born ;) Do you know any way to use a loop here because it is way over my knowledge with iseries db2. – Kaptin Oct 07 '15 at 13:00

0 Answers0