Can anyone help me write a sql query...

Question

jkdfhdjfhjh&name=ijkjkjkjkjkjk&id=kdjkjkjkjkjkjjjd&class=kdfjjfjdhfjhf

The above string has some characters starting with & and ending with = for example we have &name= and I just need this from the above string.

similarly I need &id=, &class=

I need the output under a single column.

Final Extract
----------------------
&id=, &class=, &name=

can anyone help me out in writing a query for this.

Answer 1

If you must do this in SQL (and given that your tags include postgreSQL) , look up the postgreSQL command regexp_split_to_array which will probably help you. I'm not a postgres guru so I won't try to give you a completed block of code as I'd probably get it wrong.

Answer 2

You could try this :

select regexp_replace('jkdfhdjfhjh&name=ijkjkjkjkjkjk&id=kdjkjkjkjkjkjjjd&class=kdfjjfjdhfjhf', '\\w*?(&.*?=)\\w+((?=&)|$)', '\\1, ', 'g');

result:

     regexp_replace
-------------------------
 &name=, &id=, &class=,

Then it's up to you to remove the last ,.

The regexp_replace function is available in version 8.1 and after.

Answer 3

If you want the values along with each variable, I would implement this by splitting on "&" into an array and then taking a slice of the desired elements:

SELECT (string_to_array('jkdfhdjfhjh&name=ijkjkjkjkjkjk&id=kdjkjkjkjkjkjjjd&class=kdfjjfjdhfjhf','&'))[2:4];

Output in PostgreSQL 8.4 (array type):

{name=ijkjkjkjkjkjk,id=kdjkjkjkjkjkjjjd,class=kdfjjfjdhfjhf}

The example string is very wide so here's the general form to show the array slicing more clearly:

SELECT ((string_to_array(input_field,'&'))[2:4];

NOTE: You must have the extra parentheses around the string_to_array() call in order for the array slicing to work--you'll get an error otherwise.