• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 153
  • Last Modified:

How to grab this with SQL in PostgreSQL

Hi,

here is sqlfidlle

http://sqlfiddle.com/#!11/c0c1c

I would like to get bellow result out of that...

fist  |  second
100  | 200
101  | 201
102  | 202
103  | 203
104  | 204
105  | 205

regards
0
hi4ppl
Asked:
hi4ppl
  • 2
1 Solution
 
Mark BullockQA Engineer IIICommented:
Here's a basic example. You can make it more flexible by replacing 3 with the position of the next comma.
select position('{name,' in thatfield) 
, substring( thatfield from 6+position('{name,' in thatfield) for 3) as first
, substring( thatfield from 10+position('{name,' in thatfield) for 3) as second
from table1

Open in new window

0
 
earth man2Commented:
I presume you want to work with ARRAY type columns ?
Something like the following may guide you in the right direction ..

CREATE TABLE Table1
      (ThatField text[][])
;
      
INSERT INTO Table1
      (ThatField)
VALUES ('{{secondname,200,null},{name,103,203}}'),
      ('{{name,105,205}}')
;

select thatfield[1][2] as col1,thatfield[1][3] as col2 from table1
where thatfield[1][3] is not null
union all
select thatfield[2][2] as col1,thatfield[2][3] as col2 from table1
where thatfield[1][3] is null
;
0
 
hi4pplAuthor Commented:
Hi,

thanks Mark, can you please explain this to me like specially the 6+position part

thanks
0
 
Mark BullockQA Engineer IIICommented:
The length of '{name,' is 6 characters.
6+ is to get the position of the first character following '{name,'.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now