0

I need to create a string from data that is extracted from an SQL query, and wish to accomplish it within the SQL Query.

The return is something like

+---------------+
| foo_ids       |
+---------------+
| 63901-63601   |
| 64101-63501   |
| 65101-63501   |
| 65001-63401   |
| 64902-63401   |
| 64901         |
| 62401         |
| 63901         |
+---------------+

I would like to do 2 things to it,

  1. separate strings where there are "-"s
  2. create a string of the ids separated by ","

the strings will only contain numbers and "-"

my final output desired is,

63901,63601,64101,65101,63501,65001,63401,64902,64901,62401

is this possible/realistic or should I have a script or something to fetch it and do some string manipulation in there?

  • To be honest, this can be done much simpler in the application layer. – Twinkles Apr 23 '14 at 9:17
  • Yep I think so too, but I can only access the database via sql queries, can't run any real code, just might be able to have a batch file runnable if things go well – Saifis Apr 23 '14 at 9:24
  • Which flavour of SQL does this need to be? What did you try yourself? – Colin 't Hart Apr 23 '14 at 9:28
  • Sorry about that its in Mysql, I'm not used to asking DB questions, be careful from now. will put down some things I've done. – Saifis Apr 23 '14 at 9:32
1

Please try this query:

select group_concat(foo_id separator ',') as ids from
(
select 
 case 
 when locate('-', foo_ids) > 0 then 
  substr(foo_ids, 1, locate('-', foo_ids) - 1) 
 else foo_ids 
 end as foo_id
from foo_table
union all
select 
 case 
 when locate('-', foo_ids) > 0 then 
  substr(foo_ids, (locate(foo_ids, '-') + 1), locate('-', foo_ids) - 1) 
 else null 
 end as foo_id
from foo_table
) t 
where foo_id is not null

http://sqlfiddle.com/#!2/ce1b0a/7

0

You may wish to specify which SQL brand you're running, as that will sometimes limit the availability of tools.

Easiest solution: use the replace() function that comes with almost all brands to replace ' _ ' with ','

However, that will leave you with duplicate results (for instance 63501 in your example), which are hard to remove using SQL alone, as the string value returned ('64101,63501') could still be a unique result.

If your SQL brand supports stored procedures, you could use a temp table, which you fill with the first half of separated strings and non-separated strings in a first step and the second half of separated strings in a second steps, which would allow you to filter for duplicates.

Finally you need to decide if you want to concatenate either within your stored procedure, using a builtin function like toXMLstring (if applicable in your SQL brand) or in the application layer.

Personally my preference (in SQLserver, admittedly) would be to compute the whole result in my stored procedure.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.