Sorting Values in a Column

收藏待读

Sorting Values in a Column

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBlogger s.

This was a post that caught my eye, since I』d just written a piece on STRING_SPLIT() . Someone had this data:

DECLARE @Names VARCHAR(8000) = 'Steve, Grant, Kathi, Kendra';

They wanted this returned as a string that was sorted, so that the values would be:

Grant, Kathi, Kendra, Steve

String manipulation isn』t the strength of SQL Server, but we can do this with STRING_SPLIT(). If we use STRING_SPLIT(), we get a list of values. We want to remove the spaces after the comma, so we use REPLACE to remove that.

SELECT *

FROM STRING_SPLIT(REPLACE(@Names, ' ', ''), ',')

ORDER BY value;

We can then aggregate these back together in a variable assignment, adding the comma for each row.

SELECT @newtext = @newtext + Value + ', '

FROM STRING_SPLIT(REPLACE(@Names, ' ', ''), ',')

ORDER BY value;

This gives us:

Sorting Values in a Column

SQLNewBlogger

A quick T-SQL application of some skills I learned. Can you do something similar? Maybe order dates or numbers that are in a string in the wrong order?

That should be a 10-15 minute post.

原文 : Voice of the DBA

相關閱讀

免责声明:本文内容来源于Voice of the DBA,已注明原文出处和链接,文章观点不代表立场,如若侵犯到您的权益,或涉不实谣言,敬请向我们提出检举。