Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Eliminate starting comma(s) and ending comma(s) from a string using Select

In this puzzle we have have a column from where you need to eliminate starting comma(s) and ending comma(s) from a string using Select.
Please check out the sample input values and sample expected output below.

Sample Input

a
,,2017 Q1,2018 Q2,
2017 Q3,,,
,,2017 Q2

Expected Output

a
2017 Q1,2018 Q2
2017 Q3
2017 Q2

Script

Use below script to create table and insert sample data into it.

--

SELECT a INTO #t
FROM 
(
	SELECT  ',,2017 Q1,2018 Q2,' a UNION ALL
	SELECT '2017 Q3,,,' a UNION ALL
	SELECT  ',,2017 Q2' a 
)r

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1


--

select 
		REPLACE(REPLACE(LTRIM(RTRIM(REPLACE(REPLACE(a,' ','|'),',',' '))),' ',','),'|',' ') a
FROM #t

--

Output


/*------------------------
select 
 REPLACE(REPLACE(LTRIM(RTRIM(REPLACE(REPLACE(a,' ','|'),',',' '))),' ',','),'|',' ') a
FROM #t
------------------------*/
a
---------- 
2017 Q1,2018 Q2
2017 Q3
2017 Q2

(3 row(s) affected)

Solution – 2


--

SELECT string, 
    SUBSTRING(string, leading_bad_char_count + 1, LEN(string) - leading_bad_char_count - trailing_bad_char_count) AS new_string
FROM ( VALUES(',,2017 Q1,2018 Q2,'),('2017 Q3,,,'),(',,2017 Q2'),(',,,,2017,,Q4,,,//'),('2017 q1')) AS strings(string)
CROSS APPLY (
    SELECT PATINDEX('%[0-9A-Z]%', string) - 1 AS leading_bad_char_count,
        PATINDEX('%[0-9A-Z]%', REVERSE(string)) - 1 AS trailing_bad_char_count
) AS assign_alias_names

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements