0

I have a string that looks like: '10,244,566' How do I get the 2nd item?
for the above string the item is 244.

The string always looks like 'x,y,z'. I don't see anything helpful in the manual http://www.postgresql.org/docs/9.1/static/functions-string.html#FUNCTIONS-STRING-OTHER

EDIT: The string is a text that I recive from other component that I have no control over. Assume that the user enters 3 parmeters.. this component save it as string and pass the string to me. I need to break it into the 3 paremeters. For my need i only case about the 2nd paremter.

I can do this: 

select string_to_array(string, ',', null)

for the above example it will give me {10,244,566} as 

text[]

how do it get the 2nd item in the array?

Un Peu
  • 131
  • 3
  • 13
  • 2
    Do you store data as comma separated values?!? – jarlh Dec 16 '15 at 13:42
  • 4
    [`split_part`](http://sqlfiddle.com/#!15/9eecb7db59d16c80417c72d1e1f4fbf1/5040/0) is there in the manual, but what people above and below say about [1NF](https://en.wikipedia.org/wiki/First_normal_form) is really worth considering. – bereal Dec 16 '15 at 13:46
  • @bereal make it an answer so I can accept it. thank you. – Un Peu Dec 16 '15 at 13:52
  • @UnPeu: you're agreeing with bereal on using 1NF but contradicting me on using 1NF... you're a little confusing to me :) – Marcus Müller Dec 16 '15 at 13:54
  • 1
    @MarcusMüller the OP is agreeing with me regarding `split_part`. He is not storing CSV, just the opposite. So it's not violating 1NF. I'd maybe consider preparing the data in the application layer, but that involves the details of his app I'm not aware of. – bereal Dec 16 '15 at 13:58
  • 1NF is relevent when speaking of TABLES. I don't use any table... This is an inside operation with a PostgreSQL FUNCTION. – Un Peu Dec 16 '15 at 13:59
  • yes, but PostgreSQL functions are applied to data coming from a postgresql table, usually. – Marcus Müller Dec 16 '15 at 14:05
  • @UnPeu Ah, just saw your clarification under my answer and edited my answer. Thanks! – Marcus Müller Dec 16 '15 at 14:07
  • Lets say you have func1() which open a text file read a line and return it as TEXT. Think that my function is calling : `select func1() into savetext`. and savetext is '10,244,566'. No text is saved in the DB. – Un Peu Dec 16 '15 at 14:08

4 Answers4

4

Looks like a job for split_part (sql fiddle):

select split_part('10,244,566', ',', 2);
bereal
  • 32,519
  • 6
  • 58
  • 104
1

Splitting strings contained in the database isn't really something that databases were designed for.

There's different options here:

  1. restructure your data to make strings like 10,244,566
    1. three columns, if it's always three values, or
    2. a n-to-m mapping with a second table
  2. program the splitting in whatever language you're using to talk to postgresql
  3. hack together a relatively hard to debug, relatively slow, relatively "far away from what you want" extractor in postgresql: select substring(the_column from ',\d+,') from the_table

I strongly recommend doing option 1.1. if possible, or option 1.2. if the string might not always represent three values.

As it is now, you're abusing your database's string data type to store three integers, which is a waste of space, slow, error-prone, an anti-pattern and hard to maintain.

EDIT: I have to take back a bit about you abusing a database: whoever is pushing that string into postgresql is abusing the database, so that you now have to deal with it :(

EDIT your clarification is critical:

I am proccessing the string in postgresql function. My function is connecting to another component (lets say paypal) and reciveing a data burst as STRING. I need to convert this String for items to store in my PostgreSQL DB. So I catualy saves the 2nd parmeter as integer

Awesome, so just use the programming language you're using to get your string from let's say paypal and split it to three parts there. There's no reason postgreSQL would do that better than you could, and you would have the conceptual benefit of transporting well-formed data to your database.

Marcus Müller
  • 34,677
  • 4
  • 53
  • 94
  • it's not a table. It's 3 parameters that I recive as string. I need to split them... I only need the 2nd parameter for my action... so I ask how do i get it from the string. – Un Peu Dec 16 '15 at 13:46
  • whatever you get from a database is stored in a table. You don't seem to understand what a relational database is. – Marcus Müller Dec 16 '15 at 13:48
  • Marcus you don't seem to understand what I ask. In fact @bereal solved my problem in the comment with this: `select split_part('10,244,566', ',', 2);` That is all I needed. – Un Peu Dec 16 '15 at 13:52
  • @UnPeu: I still stand by my opinion that no matter where you get that string from, you should restructure your data. Storing comma separated numerical values in a string is just so wrong! – Marcus Müller Dec 16 '15 at 13:53
  • I don't store anything... My function gets this data from an outside system.. I can not change it. I recive the string and break it. I don't save it. – Un Peu Dec 16 '15 at 13:58
  • You get the string from a postgresql database, says your question! **it is stored, somewhere!** – Marcus Müller Dec 16 '15 at 14:01
  • No.. I am proccessing the string in postgresql function. My function is connecting to another component (lets say paypal) and reciveing a data burst as STRING. I need to convert this String for items to store in my PostgreSQL DB. So I catualy saves the 2nd parmeter as integer – Un Peu Dec 16 '15 at 14:04
1

You can use a combination of substr and strpos.

SQL Fiddle

select substr(val, 
              strpos(val,',')+1, 
              length(val)-strpos(reverse(val),',')-length(substr(val, 1,strpos(val,',')))
              )
from t
Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
0

Original:

select string_to_array('10,244,566', ',', null)

Just put the array between "()" and refer to the item you want to get as simple as this "[2]":

select (string_to_array('10,244,566', ',', null))[2]
Christian
  • 7,062
  • 9
  • 53
  • 79