-1
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.

gkrogers
  • 8,126
  • 3
  • 29
  • 36
user338292
  • 105
  • 1
  • 3
  • 10
  • 2
    Which SQL dialect exactly? PostgreSQL? Regular Expressions are not part of the ANSI SQL standard AFAIK. – Pekka Sep 09 '10 at 09:46
  • 2
    Is there no server-side language you can use? In most of them, this would be one function call. – Pekka Sep 09 '10 at 09:47
  • so the string in a field in a SQL database, right? Does the solution need to be pure SQL? I'd consider it much easier to achieve this in another language. – Spudley Sep 09 '10 at 09:56

2 Answers2

1

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.

Toto
  • 89,455
  • 62
  • 89
  • 125
1

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.

Matthew Wood
  • 16,017
  • 5
  • 46
  • 35
  • Yeah, I realized that after I posted, but I assumed otherwise as I couldn't figure out what the use was of parsing out the field names and throwing away the associated values... – Matthew Wood Sep 09 '10 at 21:45