February 1, 2018 at 9:48 am
Hi
I want to sort a column value alphabetically which is already in a sql table to something like below :
Tamesha Rios, Damian Richard, Tim David --> to Damien Richard, Tamesha Rios, Tim David
Thanks,
PSB
February 1, 2018 at 10:02 am
As you're using SQL Server 2016 and ordinal position doesn't matter, have a look at STRING_SPLIT() (Transact-SQL). Then, have a look at how to create a delimited string using T-SQL; there are literally 1,000's of answer out there if you use Google. Post what you've tried if you get stuck, and we'll be happy to nudge you in the right direction.
Ideally, however, don't store your values as a delimited string in SQL Server; store each item in it's own row.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 1, 2018 at 5:00 pm
The question now is, especially since you posted in a 2016 forum, are you actually using SQL Server 2016?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 2, 2018 at 1:36 am
Jeff Moden - Thursday, February 1, 2018 5:00 PMThe question now is, especially since you posted in a 2016 forum, are you actually using SQL Server 2016?
One would hope.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 6, 2018 at 12:34 am
PSB - Thursday, February 1, 2018 9:48 AMHiI want to sort a column value alphabetically which is already in a sql table to something like below :
Tamesha Rios, Damian Richard, Tim David --> to Damien Richard, Tamesha Rios, Tim David
Thanks,
PSB
I've created a test table and small static script which produces your desired output,
SELECT * INTO #TestTable FROM (VALUES
('Tamesha Rios,Damian Richard,Tim David')
)v(CSVField);
SELECT CSVField, PARSENAME(REPLACE(CSVField,',','.'),2) + ',' + PARSENAME(REPLACE(CSVField,',','.'),3) + ',' + PARSENAME(REPLACE(CSVField,',','.'),1) 'Name' FROM #TestTable
For realtime dynamic conversion, you'd need to create an user defined function which does the same. Please keep an eye on the performance of your function as well while executing....
February 6, 2018 at 6:58 am
subramaniam.chandrasekar - Tuesday, February 6, 2018 12:34 AMPSB - Thursday, February 1, 2018 9:48 AMHiI want to sort a column value alphabetically which is already in a sql table to something like below :
Tamesha Rios, Damian Richard, Tim David --> to Damien Richard, Tamesha Rios, Tim David
Thanks,
PSBI've created a test table and small static script which produces your desired output,
SELECT * INTO #TestTable FROM (VALUES
('Tamesha Rios,Damian Richard,Tim David')
)v(CSVField);
SELECT CSVField, PARSENAME(REPLACE(CSVField,',','.'),2) + ',' + PARSENAME(REPLACE(CSVField,',','.'),3) + ',' + PARSENAME(REPLACE(CSVField,',','.'),1) 'Name' FROM #TestTable
For realtime dynamic conversion, you'd need to create an user defined function which does the same. Please keep an eye on the performance of your function as well while executing....
That's nice but it will fail if there are more than 4 names.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 6, 2018 at 7:01 am
@PSB,
Where you able to solve your problem from what has been posted so far? Even if you have, you might want to post what you've come up with because, as subramaniam.chandrasekar posted, there can be some pretty severe performance problems with these types of things and we can help.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 6, 2018 at 8:55 am
Looks like the OP has bailed on this thread but it's still an interesting request from a SQL Server versioning point of view (IMO), due to new functions that have been added in the last two releases.
Below are 3 different possible solutions that will work, depending on what version of SQL Server you're using...
IF OBJECT_ID('tempdb..#Test Data', 'U') IS NOT NULL
DROP TABLE #TestData;
CREATE TABLE #TestData (
fk_id INT NOT NULL IDENTITY(1,1),
FullNameCSV VARCHAR(8000) NOT NULL
);
INSERT #TestData (FullNameCSV) VALUES
('Tamesha Rios, Damian Richard, Tim David'),
('Jeff Moden, Thom A, PSB, Jason Long');
--================================================================================================
--------------------------------------------------------------------------------------------------
-- SS 2008 & after
SELECT
td.fk_id,
FullNameCSV_Ordered = STUFF(fco.FullNameCSV_Ordered, 1, 2, '')
FROM
#TestData td
CROSS APPLY (
SELECT
', ' + LTRIM(ds.Item)
FROM
dbo.DelimitedSplit8K(td.FullNameCSV, ',') ds --<< http://www.sqlservercentral.com/articles/Tally+Table/72993/
ORDER BY
LTRIM(ds.Item)
FOR XML PATH('')
) fco (FullNameCSV_Ordered)
GROUP BY
td.fk_id,
fco.FullNameCSV_Ordered;
--------------------------------------------------------------------------------------------------
-- SS 2016 & after
SELECT
td.fk_id,
FullNameCSV_Ordered = STUFF(fco.FullNameCSV_Ordered, 1, 2, '')
FROM
#TestData td
CROSS APPLY (
SELECT
', ' + LTRIM(ss.value)
FROM
STRING_SPLIT(td.FullNameCSV, ',') ss --<< STRING_SPLIT() became available in 2016
ORDER BY
LTRIM(ss.value)
FOR XML PATH('')
) fco (FullNameCSV_Ordered)
GROUP BY
td.fk_id,
fco.FullNameCSV_Ordered;
--------------------------------------------------------------------------------------------------
-- SS 2017
SELECT
td.fk_id,
FullNameCSV_Ordered = STRING_AGG(LTRIM(ss.value), ', ') WITHIN GROUP (ORDER BY LTRIM(ss.value)) --<< STRING_AGG() became available in 2017
FROM
#TestData td
CROSS APPLY STRING_SPLIT(td.FullNameCSV, ',') ss
GROUP BY
td.fk_id;
February 6, 2018 at 9:57 am
Jason A. Long - Tuesday, February 6, 2018 8:55 AMLooks like the OP has bailed on this thread but it's still an interesting request from a SQL Server versioning point of view (IMO), due to new functions that have been added in the last two releases.Below are 3 different possible solutions that will work, depending on what version of SQL Server you're using...
IF OBJECT_ID('tempdb..#Test Data', 'U') IS NOT NULL
DROP TABLE #TestData;
CREATE TABLE #TestData (
fk_id INT NOT NULL IDENTITY(1,1),
FullNameCSV VARCHAR(8000) NOT NULL
);
INSERT #TestData (FullNameCSV) VALUES
('Tamesha Rios, Damian Richard, Tim David'),
('Jeff Moden, Thom A, PSB, Jason Long');
--================================================================================================
--------------------------------------------------------------------------------------------------
-- SS 2008 & after
SELECT
td.fk_id,
FullNameCSV_Ordered = STUFF(fco.FullNameCSV_Ordered, 1, 2, '')
FROM
#TestData td
CROSS APPLY (
SELECT
', ' + LTRIM(ds.Item)
FROM
dbo.DelimitedSplit8K(td.FullNameCSV, ',') ds --<< http://www.sqlservercentral.com/articles/Tally+Table/72993/
ORDER BY
LTRIM(ds.Item)
FOR XML PATH('')
) fco (FullNameCSV_Ordered)
GROUP BY
td.fk_id,
fco.FullNameCSV_Ordered;
--------------------------------------------------------------------------------------------------
-- SS 2016 & after
SELECT
td.fk_id,
FullNameCSV_Ordered = STUFF(fco.FullNameCSV_Ordered, 1, 2, '')
FROM
#TestData td
CROSS APPLY (
SELECT
', ' + LTRIM(ss.value)
FROM
STRING_SPLIT(td.FullNameCSV, ',') ss --<< STRING_SPLIT() became available in 2016
ORDER BY
LTRIM(ss.value)
FOR XML PATH('')
) fco (FullNameCSV_Ordered)
GROUP BY
td.fk_id,
fco.FullNameCSV_Ordered;
--------------------------------------------------------------------------------------------------
-- SS 2017
SELECT
td.fk_id,
FullNameCSV_Ordered = STRING_AGG(LTRIM(ss.value), ', ') WITHIN GROUP (ORDER BY LTRIM(ss.value)) --<< STRING_AGG() became available in 2017
FROM
#TestData td
CROSS APPLY STRING_SPLIT(td.FullNameCSV, ',') ss
GROUP BY
td.fk_id;
Very cool summary.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 6, 2018 at 10:27 am
your DROP TABLE statement has an extra space, here's the corrected version, and I added in one in the 2008 & After section if you wanted to order by lastname instead of firstname (didn't modify the others because I'm stuck in 2014 at the moment) :
IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
DROP TABLE #TestData;
CREATE TABLE #TestData (
fk_id INT NOT NULL IDENTITY(1,1),
FullNameCSV VARCHAR(8000) NOT NULL
);
INSERT #TestData (FullNameCSV) VALUES
('Tamesha Rios, Damian Richard, Tim David'),
('Jeff Moden, Thom A, PSB, Jason Long');
--================================================================================================
--------------------------------------------------------------------------------------------------
-- SS 2008 & after
SELECT
td.fk_id,
FullNameCSV_Ordered = STUFF(fco.FullNameCSV_Ordered, 1, 2, '')
FROM
#TestData td
CROSS APPLY (
SELECT
', ' + LTRIM(ds.Item)
FROM
dbo.DelimitedSplit8K(td.FullNameCSV, ',') ds --<< http://www.sqlservercentral.com/articles/Tally+Table/72993/
ORDER BY
LTRIM(ds.Item)
FOR XML PATH('')
) fco (FullNameCSV_Ordered)
GROUP BY
td.fk_id,
fco.FullNameCSV_Ordered;
--------------------------------------------------------------------------------------------------
-- SS 2008 & after - Lastname first
SELECT
td.fk_id,
FullNameCSV_Ordered = STUFF(fco.FullNameCSV_Ordered, 1, 2, '')
FROM
#TestData td
CROSS APPLY (
SELECT
', ' + substring(LTRIM(ds.Item),charindex(' ',LTRIM(ds.Item))+1,len(ds.Item)) + ', '+ substring(Ltrim(ds.Item),1,charindex(' ',Ltrim(ds.Item)))
FROM
dbo.DelimitedSplit8K(td.FullNameCSV, ',') ds --<< http://www.sqlservercentral.com/articles/Tally+Table/72993/
ORDER BY
--LTRIM(ds.Item)
', ' + substring(LTRIM(ds.Item),charindex(' ',LTRIM(ds.Item))+1,len(ds.Item)) + ', '+ substring(Ltrim(ds.Item),1,charindex(' ',Ltrim(ds.Item)))
FOR XML PATH('')
) fco (FullNameCSV_Ordered)
GROUP BY
td.fk_id,
fco.FullNameCSV_Ordered;
--------------------------------------------------------------------------------------------------
-- SS 2016 & after
SELECT
td.fk_id,
FullNameCSV_Ordered = STUFF(fco.FullNameCSV_Ordered, 1, 2, '')
FROM
#TestData td
CROSS APPLY (
SELECT
', ' + LTRIM(ss.value)
FROM
STRING_SPLIT(td.FullNameCSV, ',') ss --<< STRING_SPLIT() became available in 2016
ORDER BY
LTRIM(ss.value)
FOR XML PATH('')
) fco (FullNameCSV_Ordered)
GROUP BY
td.fk_id,
fco.FullNameCSV_Ordered;
--------------------------------------------------------------------------------------------------
-- SS 2017
SELECT
td.fk_id,
FullNameCSV_Ordered = STRING_AGG(LTRIM(ss.value), ', ') WITHIN GROUP (ORDER BY LTRIM(ss.value)) --<< STRING_AGG() became available in 2017
FROM
#TestData td
CROSS APPLY STRING_SPLIT(td.FullNameCSV, ',') ss
GROUP BY
td.fk_id;
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
February 6, 2018 at 12:35 pm
Jeff Moden - Tuesday, February 6, 2018 9:57 AMVery cool summary.
Thank you sir!
February 6, 2018 at 1:25 pm
jonathan.crawford - Tuesday, February 6, 2018 10:27 AMyour DROP TABLE statement has an extra space, here's the corrected version, and I added in one in the 2008 & After section if you wanted to order by lastname instead of firstname (didn't modify the others because I'm stuck in 2014 at the moment) :
IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
DROP TABLE #TestData;
CREATE TABLE #TestData (
fk_id INT NOT NULL IDENTITY(1,1),
FullNameCSV VARCHAR(8000) NOT NULL
);
INSERT #TestData (FullNameCSV) VALUES
('Tamesha Rios, Damian Richard, Tim David'),
('Jeff Moden, Thom A, PSB, Jason Long');
--================================================================================================
--------------------------------------------------------------------------------------------------
-- SS 2008 & after
SELECT
td.fk_id,
FullNameCSV_Ordered = STUFF(fco.FullNameCSV_Ordered, 1, 2, '')
FROM
#TestData td
CROSS APPLY (
SELECT
', ' + LTRIM(ds.Item)
FROM
dbo.DelimitedSplit8K(td.FullNameCSV, ',') ds --<< http://www.sqlservercentral.com/articles/Tally+Table/72993/
ORDER BY
LTRIM(ds.Item)
FOR XML PATH('')
) fco (FullNameCSV_Ordered)
GROUP BY
td.fk_id,
fco.FullNameCSV_Ordered;
--------------------------------------------------------------------------------------------------
-- SS 2008 & after - Lastname first
SELECT
td.fk_id,
FullNameCSV_Ordered = STUFF(fco.FullNameCSV_Ordered, 1, 2, '')
FROM
#TestData td
CROSS APPLY (
SELECT
', ' + substring(LTRIM(ds.Item),charindex(' ',LTRIM(ds.Item))+1,len(ds.Item)) + ', '+ substring(Ltrim(ds.Item),1,charindex(' ',Ltrim(ds.Item)))
FROM
dbo.DelimitedSplit8K(td.FullNameCSV, ',') ds --<< http://www.sqlservercentral.com/articles/Tally+Table/72993/
ORDER BY
--LTRIM(ds.Item)
', ' + substring(LTRIM(ds.Item),charindex(' ',LTRIM(ds.Item))+1,len(ds.Item)) + ', '+ substring(Ltrim(ds.Item),1,charindex(' ',Ltrim(ds.Item)))
FOR XML PATH('')
) fco (FullNameCSV_Ordered)
GROUP BY
td.fk_id,
fco.FullNameCSV_Ordered;
--------------------------------------------------------------------------------------------------
-- SS 2016 & after
SELECT
td.fk_id,
FullNameCSV_Ordered = STUFF(fco.FullNameCSV_Ordered, 1, 2, '')
FROM
#TestData td
CROSS APPLY (
SELECT
', ' + LTRIM(ss.value)
FROM
STRING_SPLIT(td.FullNameCSV, ',') ss --<< STRING_SPLIT() became available in 2016
ORDER BY
LTRIM(ss.value)
FOR XML PATH('')
) fco (FullNameCSV_Ordered)
GROUP BY
td.fk_id,
fco.FullNameCSV_Ordered;
--------------------------------------------------------------------------------------------------
-- SS 2017
SELECT
td.fk_id,
FullNameCSV_Ordered = STRING_AGG(LTRIM(ss.value), ', ') WITHIN GROUP (ORDER BY LTRIM(ss.value)) --<< STRING_AGG() became available in 2017
FROM
#TestData td
CROSS APPLY STRING_SPLIT(td.FullNameCSV, ',') ss
GROUP BY
td.fk_id;
I don't see what you're talking about with the extra space thing... It's just, "DROP TABLE #TestData;", which has the exactly 1 space between words... not that it actually matters...
As far as doing the sort by last name... You're making a dangerous, and usually faulty, assumption... IE, that each individual only has one or two names. Throw in a middle name and now your solution is sorting by middle name not last name.
Add the fact that many people have 2 (or more) middle names and/or double surnames, trying to problematically parse, classify & sort name parts is actually an extremely complex task that isn't likely to ever be 100% correct.
Some examples:
Vincent Van Gogh
Leonardo da Vinci
Helena Bonham Carter
John Ronald Reuel Tolkien (JRR Tolkien)
George Raymond Richard Martin
The moral of the story... Unless it's an absolute business requirement, DO NOT offer to do this. If it is a requirement, made sure you get VERY explicit rules as to how the business wants you to determine the the last name... Odds are the business person making the request didn't put much thought into the complexities of the task and you don't want to be the one who gets held responsible for bad sorts in a business critical application.
Just my 2 cents...
February 6, 2018 at 1:39 pm
Agreed on the rest.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 6, 2018 at 1:54 pm
Jeff Moden - Tuesday, February 6, 2018 1:39 PMThe extra space was in your IF before the DROP, not in the actual DROP itself.Agreed on the rest.
"I see", said the blind man as he picked up his hammer and saw...
Not sure how I let that happen... Good catch! Both of you.
February 6, 2018 at 3:11 pm
Jason A. Long - Tuesday, February 6, 2018 1:54 PMJeff Moden - Tuesday, February 6, 2018 1:39 PMThe extra space was in your IF before the DROP, not in the actual DROP itself.Agreed on the rest.
"I see", said the blind man as he picked up his hammer and saw...
Not sure how I let that happen... Good catch! Both of you.
Heh... in the land of the blind, the one eyed man is king.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy