how to get value from a field in table

Question

I have a field named 'parameter' in table with below format

utf8: "\xE2\x9C\x93"
id: "805265"
plan: initial
acc: "123456"
last: "1234"
doc: "1281468479"
validation: field
commit: Accept

how to query 'acc' value from the table?

database :sequelPro

When you save into parameter you should save as a json. Then you can search a json object — MZaragoza, Jan 09 '15 at 01:34
i cant save it as a json object. i need to save this value into another field — jack, Jan 09 '15 at 03:07

score 1 · Answer 1 · answered Jan 09 '15 at 01:36

1

> str = 'utf8..... your string here...'
> puts str
utf8: "\xE2\x9C\x93"
id: "805265"
plan: initial
acc: "123456"
last: "1234"
doc: "1281468479"
validation: field
commit: Accept
=> nil
> str.match(/^acc: "(\d+)"/).captures.first
=> "123456"

answered Jan 09 '15 at 01:36

Philip Hallstrom

19,673
2
42
46

Vivek S. · Answer 2 · 2015-01-09T07:07:10.303

0

If I understood correctly, In PostgreSQL use split_part

See this EXAMPLE

create table star (param text);
insert into star values ('utf8: "\xE2\x9C\x93"'),
                        ('id: "805265"'),
                        ('plan: initial'),
                        ('acc: "123456"'),
                        ('last: "1234"'),
                        ('doc: "1281468479"'),
                        ('validation: field'),
                        ('commit: Accept');

and use function split_part in SELECT query to get value of acc: like this

select col2 
from (
      select split_part(param, ' ', 1) col1,
             split_part(param, ' ', 2) col2 
      from star
     ) t where col1='acc:'

Note: if you want to split your field by : then use select split_part(param, ':', 1) and split_part(param, ':', 2) col2 so the WHERE clause should be where col1='acc'

sqlfiddle-demo

edited Jan 09 '15 at 07:07

answered Jan 09 '15 at 04:43

Vivek S.

19,945
7
68
85

@jack I've created table just for an **example** no need to create table in your case :D(_you already have the table with data but think in my case i dont have any table or data like your's so i need to create one to test right ?_), just query like I've described in my answer – Vivek S. Jan 09 '15 at 07:04
@jack `select col2 from ( select split_part(_YOUR_COLUMN/FIELD_, ':', 1) col1, split_part(_YOUR_COLUMN/FIELD_, ':', 2) col2 from _YOUR_TABLE_NAME_ )t where col1='acc' ` – Vivek S. Jan 09 '15 at 07:09
my database doesn't support split_part – jack Jan 09 '15 at 20:44
@jack what's your DB and It's version ? – Vivek S. Jan 13 '15 at 11:14

how to get value from a field in table

2 Answers2