SQL tricky Interview Questions | How to replace multiple commas with a single comma

How to install SQL Server for practice?

Check out the complete list of SQL Query Interview Questions -

Best Data Science / Analytics / SQL courses
Learn SQL Basics for Data Science Specialization

Data Science Fundamentals with Python and SQL Specialization

Python for Everybody Specialization

Google Data Analytics Professional Certificate

Coursera Plus - Data Science Career Skills

Please do not forget to like, subscribe and share.

For enrolling and enquiries, please contact us at

[iwebChristophe]

Even i wouldn't go for REPLACE() function, i do actually like the algorythm idea behind this approach. Take a 2 char delimiter -> put in -> reverse it -> replace by blank ... This is kinda an interesting algo that might be applied in many other problem i feel.
You earned a like ;)

[vincentsolus]

I think it would important to note that you auxliar character (or how ever you wana call it, the one you add to the pattern) must be guaranted to not be present in the string you gonna work with, at least not forming the pattern you are going to use, you can use some obscure character on the char set you are using, but depending where your string comes from, is hard to guarante anything about it contents.

I would say that you can also use a regular expression, but i just found out that SQLServer does not have regex_replace function at all... but for other SGBD, you may can use a regex_replace with the simple regular expression ', +' replaced by a single coma, maybe is less performatic (not sure how regex replace perfoms against 3 normal replaces), but is simple, elegant, and is agnostic to input content

[Kabboch]

I achieve the same by using string_split() then stuff() the result removing nulls and empty values adding, before each value starting from digit number 1

[ColtonSpears]

DECLARE @str VARCHAR(50) =

SELECT STRING_AGG(value, ', ')
WHERE LEN(value) > 0

[LJBJams]

Great lessons by the way. I was wondering if this could be done with regular expressions in sql? I have seen regex in sql before. Thanks

[florincopaci]

I apologize because is so long-Problem 2153: Buses and passengers arrive at a station. If a bus arrives at the station at a time tbus
and a passenger arrives at a time tpassenger where tpassenger <= tbus,
then the passenger will attempt to use the first available bus whose capacity has not been exceeded.
If at the moment the bus arrives at the station there are more passengers waiting than its capacity capacity,
only capacity passengers will use the bus.

Write a SQL query to report the users that appear on each bus (if two passengers arrive at the same time,
then the passenger with the smaller passenger_id value should be given priority).
The query result format is in the following example (schema and table descriptions appear at the end of this post).

Example
Input:

Buses table:

| bus_id | arrival_time | capacity |

| 1 | 2 | 1 |
| 2 | 4 | 10 |
| 3 | 7 | 2 |


Passengers table:

| passenger_id | arrival_time |

| 11 | 1 |
| 12 | 1 |
| 13 | 5 |
| 14 | 6 |
| 15 | 7 |

Output:


| bus_id | capacity | b_arrival | spot | passenger_id | p_arrival |

| 1 | 1 | 2 | 1 | 11 | 1 |
| 2 | 10 | 4 | 1 | 12 | 1 |
| 2 | 10 | 4 | 2 | NULL | NULL |
| 2 | 10 | 4 | 3 | NULL | NULL |
| 2 | 10 | 4 | 4 | NULL | NULL |
| 2 | 10 | 4 | 5 | NULL | NULL |
| 2 | 10 | 4 | 6 | NULL | NULL |
| 2 | 10 | 4 | 7 | NULL | NULL |
| 2 | 10 | 4 | 8 | NULL | NULL |
| 2 | 10 | 4 | 9 | NULL | NULL |
| 2 | 10 | 4 | 10 | NULL | NULL |
| 3 | 2 | 7 | 1 | 13 | 5 |
| 3 | 2 | 7 | 2 | 14 | 6 |

Explanation:

Passenger 11 arrives at time 1.
Passenger 12 arrives at time 1.
Bus 1 arrives at time 2 and collects passenger 11 as it has one empty seat.
Bus 2 arrives at time 4 and collects passenger 12 as it has ten empty seats.
Passenger 13 arrives at time 5.
Passenger 14 arrives at time 6.
Passenger 15 arrives at time 7.
Bus 3 arrives at time 7 and collects passengers 13 and 14 as it has two empty seats.

Attempt
The CTE
SQL SERVER
WITH RECURSIVE bus_spots AS (
SELECT B.bus_id, B.arrival_time AS b_arrival, B.capacity, 1 AS spot FROM Buses B
UNION ALL
SELECT BS.bus_id, BS.b_arrival, BS.capacity, BS.spot + 1 FROM bus_spots BS WHERE BS.spot < BS.capacity
) SELECT * FROM bus_spots ORDER BY bus_id, spot;
gives


| bus_id | b_arrival | capacity | spot |

| 1 | 2 | 1 | 1 |
| 2 | 4 | 10 | 1 |
| 2 | 4 | 10 | 2 |
| 2 | 4 | 10 | 3 |
| 2 | 4 | 10 | 4 |
| 2 | 4 | 10 | 5 |
| 2 | 4 | 10 | 6 |
| 2 | 4 | 10 | 7 |
| 2 | 4 | 10 | 8 |
| 2 | 4 | 10 | 9 |
| 2 | 4 | 10 | 10 |
| 3 | 7 | 2 | 1 |
| 3 | 7 | 2 | 2 |

as its result set while

WITH bus_pas AS (
SELECT
passenger_id,
arrival_time AS p_arrival,
ROW_NUMBER() OVER(ORDER BY arrival_time) AS how_many
FROM Passengers
) SELECT * FROM bus_pas ORDER BY 1;
gives


| passenger_id | p_arrival | queue_pos |

| 11 | 1 | 1 |
| 12 | 1 | 2 |
| 13 | 5 | 3 |
| 14 | 6 | 4 |
| 15 | 7 | 5 |

[huajiaodjAI]

Thank you! Interesting question and answer!

[jayakrishna-xxio]

solving sql tricky questions is just like dopamine ✌✌

[karthikawesome]

Thanks for the videos 🥺 they're very helpful

[Faisal]

Question there were 4, * in string so replace qry will replaces 1 blank value, not 4 blank spaces?

[anburenganathan]

Thanks for the video.. How can we replace ' single quote for ex

[Freakouts_and_found_Insane]

Really appreciate your videos, learn a lot ❤️❤️😘

[landchennai]

select Stri, STRING_AGG(value, ', ')
from (values )v(Stri)
cross apply string_split(Stri, ', ')
where value !=''
group by Stri

[florincopaci]

Thank you for this video! this is very useful like all your videos! i have a huge request -if is possible because i noticed you don t do videos about questions from specific sites but it is a question 2153 on leetcode, a nightmare for months, from all the questions from there, sql questions, this is....Any way thank you for all the videos!

[MoofyCakes]

Wouldn't a Regular Expression Replace be more efficient?

[shrimohan]

Kindly create a video for string_agg function with issue of 8000 characters limit issue.
Thanks for making videos on core concepts.
Shubhkaamnayein

[janekslv]

Cool, but the expression will work incorrectly if initial string already contains star-commas.

[kiranpatil]

Write query to display employee having id 101, 102, 103 as per below order 101, 103, 102

[manojupadhyay]

This approach will not work if i already have *, in the text.

like

[rajshekharkategar]

This can also be solved with regex function