0

A while ago, I asked "How can I generate a breadcrumb of Categories in pure MySQL?", which a fellow stackoverflow member provided this neat code for my MySQL needs:

select group_concat(t2.name order by locate(concat('/', t2.id, '/'), concat(t1.path, '/')) separator ' - ') breadcrumb
from mdl_course_categories t1, 
     mdl_course_categories t2
where locate(concat('/', t2.id, '/'), concat(t1.path, '/'))

Today, I find I need an SQL Server 2016 solution to this combination of functions (group_concat() and locate()) to replicate this functionality. I tried running this code against this database, but I am hit with this error message instead:

SQLState: 42000

Error Code: 156

Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'order'.

I checked other questions, but was unable to transfer this knowledge to my needs. How can I replicate this functionality in SQL Server 2016?

Edit: I've read the links that @Bacon Bits provided (thank you!), but doing this in SQL Server 2016 seems as possible as programming a slice of cheese to fly to the moon. Unfortunately upgrading is not an option, so I'm stuck with this hot mess. All I need to do is replace the numbers in the path column with the name as per the id. E.g.

| id      | name          | path              | should display as              |
|---------|---------------|-------------------|--------------------------------|
| 1       | Fruit and Veg | /1                | Fruit and Veg - Fruit          |
| 436547  | Fruit         | /1/436547         | Fruit and Veg - Fruit          |
| 4657598 | Apples        | /1/436547/4657598 | Fruit and Veg - Fruit - Apples |

SO FRUSTRATING! Here's my code so far:

select 
    stuff((',' + t2.name), 1, 1, charindex(concat('/', t2.id, '/'), concat(t1.path, '/')))   
from prefix_course_categories t1, 
     prefix_course_categories t2
where charindex(concat('/', t2.id, '/'), concat(t1.path, '/'))

This produces the following error:

SQLState: 42000

Error Code: 4145

Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]An expression of non-boolean type specified in a context where a condition is expected, near 'ORDER'.

Help appreciated, thank you.

Community
  • 1
  • 1
Zectzozda
  • 77
  • 7

1 Answers1

2

GROUP_CONCAT() doesn't have a good equivalent in MS SQL Server until SQL Server 2017, when the STRING_AGG() function is introduced. SQL Server 2016 and earlier can fake it with the STUFF FOR XML PATH method, which is arcane, obnoxious, irritating, and has pitfalls where you can have XML entities in the output if you don't call it just right. But it does generally perform fairly well.

MySQL's LOCATE() is roughly equivalent to CHARINDEX(), I think. There's also the PATINDEX() function, which is a bit more flexible but doesn't perform as well.

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
  • I agree - the `stuff()` function is not easy! Thanks for the links - very helpful! – Zectzozda Apr 08 '20 at 04:16
  • Still getting stuck with using these functions, as my situation is somewhat more complex than those questions. I've edited my OP with my current attempt. Any ideas? – Zectzozda Apr 09 '20 at 01:52