February 6, 2018 at 3:44 pm
Jeff Moden - Tuesday, February 6, 2018 3:11 PMJason 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. 😉
Just went back and looked at my original test query (gotta love SQL Prompt's Tab History)... The space wasn't there. I'm going to blame this one on Chrome's auto-correct.
February 7, 2018 at 7:29 am
Hm, thanks for the lesson on sorting too, very true, had not considered that. Maybe I can avoid that trap now!
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
February 7, 2018 at 8:33 am
Thom A - Thursday, February 1, 2018 10:02 AMAs 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.
Thanks very much, link for STRING_SPLIT()....will be interesting to test against udf about performance.
February 7, 2018 at 8:53 am
Conficker - Wednesday, February 7, 2018 8:33 AMThom A - Thursday, February 1, 2018 10:02 AMAs 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.
Thanks very much, link for STRING_SPLIT()....will be interesting to test against udf about performance.
Before you do any testing, make sure you know what actually needs to be tested and what the test data needs to look like. "The Devil's in the Data". Please see the following article. It's a bit long but very well worth the read.
http://www.sqlservercentral.com/articles/STRING_SPLIT/139338/
--Jeff Moden
Change is inevitable... Change for the better is not.
February 7, 2018 at 1:06 pm
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
You are violating First Normal Form, which is the whole foundation of everything Dr. Codd did, and the whole concept of tiered architecture's that came with the software revolution in the 1970s. If this list is supposed to be a value in a column, then we need to be able to do theta operations on it. Can you tell me if "a,b,c" . is equal to "c,b,a" so we can do joins on it? I'm going to guess that your mistake is that you're trying to format data in a query. We don't do that in RDBMS or it or any other tiered architecture. The purpose of the database tier is to get the data, put it in a standardized format, and "throw it over the wall" to another tier. This tier can be presentations, calculations, or anything else. But those tears know that everything that's coming over is formatted one and only one way. And uses certain industry standards.
Would you like a kludge so you'll never be a good SQL programmer? I hope not.
Please post DDL and follow ANSI/ISO standards when asking for help.
February 7, 2018 at 2:15 pm
jcelko212 32090 - Wednesday, February 7, 2018 1:06 PMPSB - 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,
PSBYou are violating First Normal Form, which is the whole foundation of everything Dr. Codd did, and the whole concept of tiered architecture's that came with the software revolution in the 1970s. If this list is supposed to be a value in a column, then we need to be able to do theta operations on it. Can you tell me if "a,b,c" . is equal to "c,b,a" so we can do joins on it? I'm going to guess that your mistake is that you're trying to format data in a query. We don't do that in RDBMS or it or any other tiered architecture. The purpose of the database tier is to get the data, put it in a standardized format, and "throw it over the wall" to another tier. This tier can be presentations, calculations, or anything else. But those tears know that everything that's coming over is formatted one and only one way. And uses certain industry standards.
Would you like a kludge so you'll never be a good SQL programmer? I hope not.
Joe... You're making the assumption that the OP is responsible for the schema design.
Hopefully no one who's been a SSC member for 11 years would create such an abomination. Instead, I choose to give the benefit of the doubt, and assume that he/she has inherited a bad schema that they aren't able to alter.
I'm pretty sure most DBAs recite the Serenity Prayer at least half a dozen times a day... The alcoholic ones, a full dozen...
February 7, 2018 at 2:31 pm
jcelko212 32090 - Wednesday, February 7, 2018 1:06 PMPSB - 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,
PSBYou are violating First Normal Form, which is the whole foundation of everything Dr. Codd did, and the whole concept of tiered architecture's that came with the software revolution in the 1970s. If this list is supposed to be a value in a column, then we need to be able to do theta operations on it. Can you tell me if "a,b,c" . is equal to "c,b,a" so we can do joins on it? I'm going to guess that your mistake is that you're trying to format data in a query. We don't do that in RDBMS or it or any other tiered architecture. The purpose of the database tier is to get the data, put it in a standardized format, and "throw it over the wall" to another tier. This tier can be presentations, calculations, or anything else. But those tears know that everything that's coming over is formatted one and only one way. And uses certain industry standards.
Would you like a kludge so you'll never be a good SQL programmer? I hope not.
You should write a spiel about people that store XML, JSON and multi-meaning data in their permanent tables. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 7, 2018 at 5:33 pm
Jason A. Long - Tuesday, February 6, 2018 1:25 PMAdd 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
[SNIP]
Very, very minor point. Leonardo da Vinci has only one name, Leonardo . Because of that, most biographers will also note his place of birth, being in or near the town of Vinci near Florence in Italy. da Vinci is not a surname...
(Some will even note his father's name to distinguish him - Leonardo di ser Piero da Vinci)
February 7, 2018 at 7:22 pm
michael.cole 47030 - Wednesday, February 7, 2018 5:33 PMJason A. Long - Tuesday, February 6, 2018 1:25 PMAdd 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
[SNIP]Very, very minor point. Leonardo da Vinci has only one name, Leonardo . Because of that, most biographers will also note his place of birth, being in or near the town of Vinci near Florence in Italy. da Vinci is not a surname...
(Some will even note his father's name to distinguish him - Leonardo di ser Piero da Vinci)
Debatable... Most surnames originated by place of origin or family trade. Either way, your point only serves to demonstrate that programmatically parsing full names into their component parts and trying to categorize those parts into given name, middle name & surname is a near impossible task.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply