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

How to install SQL Server for practice?
• How to install SQL Ser...
Check out the complete list of SQL Query Interview Questions -
• SQL Query Interview Qu...
Best Data Science / Analytics / SQL courses
Learn SQL Basics for Data Science Specialization
imp.i384100.net/qnXYk5
Data Science Fundamentals with Python and SQL Specialization
imp.i384100.net/mgVYre
Python for Everybody Specialization
imp.i384100.net/DVz7Aj
Google Data Analytics Professional Certificate
imp.i384100.net/OR37oQ
Coursera Plus - Data Science Career Skills
imp.i384100.net/c/3299742/132...
Please do not forget to like, subscribe and share.
For enrolling and enquiries, please contact us at
Website - knowstar.org/
Facebook - / knowstartrainings
Linkedin - www.linkedin.com/company/know...
Email - [e-mail]

[@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

[@vincentsolus9227]

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

[@monika78165]

Very useful

[@ColtonSpears]

DECLARE @str VARCHAR(50) = 'abc,,def,,,,,ghi,jkl,,,,,mno'
SELECT STRING_AGG(value, ',')
FROM STRING_SPLIT(@str,',')
WHERE LEN(value) > 0

[@ilanmazuz]

its great, but This will not work in sql 2016, so the approach in the video is still viable

[@djjiang3718]

Thank you! Interesting question and answer!

[@LearnatKnowstar]

Thank you

[@karthikawesome9214]

Thanks for the videos 🥺 they're very helpful

[@LearnatKnowstar]

Thank you so much

[@Freakouts_and_found_Insane]

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

[@LearnatKnowstar]

Thank you so much!

[@hv0032113]

Very helpful. Thank you

[@LearnatKnowstar]

Thank you

[@bhubert]

Very elegant

[@LearnatKnowstar]

Thank you 🙏

[@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 ;)

[@LearnatKnowstar]

Thank you for your support 👍

[@Update_Yourself8055]

Great Ma'am, thanks a lot ❤️

[@LearnatKnowstar]

Glad it was helpful

[@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

[@herbertgzb]

No, it won’t work as the string split result will be in random sequence, not the sequence you split them from
OP’s way is a smart and clean way, and most importantly, a correct way

[@shekhark1139]

Nice trick, good hardwork 😊

[@LearnatKnowstar]

Thank you

[@nikolaybaranov2213]

Useful, thanks!

[@LearnatKnowstar]

Thank you

[@Faisal1504]

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

[@kingstonxavier]

Thank you!

[@LearnatKnowstar]

Thank you

[@florincopaci6821]

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!

[@LearnatKnowstar]

Thank you so much. Please post a link to the question and we will surely try to answer it.

[@florincopaci6821]

@@LearnatKnowstar Thank you so much.i will put here all the requirements because i don t have a leetcode subscription to let the link.Thank you

[@josephstanleyxavier7655]

Awesome 👏 thought

[@LearnatKnowstar]

Thank you

[@shamiulimran6929]

good one

[@LearnatKnowstar]

Thank you

[@anburenganathan8369]

Thanks for the video.. How can we replace ' single quote for ex (a''''''bc''c''')

[@dimpyrathore818]

Thanks a lot

[@LearnatKnowstar]

Thank you

[@Danieltdz]

very creative

[@LearnatKnowstar]

Thank you

[@jayakrishna-xx4io]

solving sql tricky questions is just like dopamine ✌✌

[@MoofyCakes]

Wouldn't a Regular Expression Replace be more efficient?

[@shrimohan1]

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

[@LearnatKnowstar]

Thank you. Surely will plan a video soon.

[@PujaKiPyaariDuniya]

While using string_agg you can check for the length and then use case statement for 1-7999 as string agg statement and >8000 use string agg+ string concat( truncated)

[@reduxThe1]

Try casting whatever you're aggregating to a LOB type e.g., varchar(max).

[@shrimohan1]

@@reduxThe1 resolved, lot of thnx

[@vaibhavkhairnar7313]

your voice is so sweet

[@LearnatKnowstar]

Thank you!

[@patricior7300]

❤

[@LearnatKnowstar]

Thank you

[@kiranpatil4968]

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

[@rajshekharkategar]

This can also be solved with regex function

[@mehmetnalbant1383]

I was going to ask the same but this is also a great video. Can you provide the sample code on how to do it with regex?

[@Sttuey]

Only if the product supports regex - SQL Server used here [still] has no native support.

[@florincopaci6821]

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

[@LearnatKnowstar]

Thank you for posting this. We will try to answer this in coming videos!

[@florincopaci6821]

@@LearnatKnowstar thank you so much

[@whitecrowuk575]

Thanks for this exercise did scratch my head for few hours how to approach this problem and this is best I could come up with (in the end if solution is simple and works is a good solution):
DECLARE @id INT
DECLARE @arrival int
DECLARE cur CURSOR FOR
SELECT p.passenger_id, P.arrival_time
FROM dbo.passenger p
ORDER BY p.arrival_time, p.passenger_id
OPEN cur
FETCH NEXT FROM cur INTO @id, @arrival;
WITH cte_bus (bus_id, capacity, b_arrival, spot)
AS (
SELECT bus_id, capacity, arrival_time AS b_arrival, 1 AS spot FROM bus
UNION ALL
SELECT bus_id, capacity, b_arrival, spot+1 FROM cte_bus
WHERE spot < capacity
)
SELECT *, NULL AS passenger_id, NULL AS p_arrival
INTO #tmp
FROM cte_bus ORDER BY bus_id, spot

WHILE @@FETCH_STATUS = 0 BEGIN
UPDATE TOP (1) #tmp SET passenger_id = @id, p_arrival = @arrival WHERE b_arrival>=@arrival AND passenger_id IS NULL
FETCH NEXT FROM cur INTO @id, @arrival
END
CLOSE cur
DEALLOCATE cur
SELECT * FROM #tmp t
DROP TABLE #tmp

[@florincopaci6821]

@@whitecrowuk575 Hello i suppose you are referring at the video and not at my problem.Thank you

[@whitecrowuk575]

@@florincopaci6821 no, your problem with bus and passengers

[@sravanikumari2937]

Hi

[@janekslv]

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

[@LearnatKnowstar]

Thank you. Will need to use another set of characters in that case

[@klavesin]

@@LearnatKnowstar unless it's a universal function - you never know which other character is a valid one

[@manojupadhyay5106]

This approach will not work if i already have *, in the text.
like "abc*,,,,,,,cde,def,,fgh,,

[@dillydilly8739]

The sample you gave would work. It does not work if you have an asterisk that follows a single comma, as in "abc,*def,,,ghi" Regardless, the input string must be considered in determining the replace character when using replace. Good tutorial!

[@velo1337]

just replace 2 commas with 1 comma and then replace 3 commas with 1 job done in 2 steps

[@timur2887]

Thats a bad approach in development. Never accept such situations, when you concate empty strings, exclude them on concatenation instead of post processing!