August 21, 2020 at 3:27 pm
Hi,
I want to remove ;# coming from SharePoint . The first and last occurrences should be removed and if there are more than one values then I want to replace the middle values with a ,comma and space . Example Q1, Q2, Q3
Thanks,
PSB
CREATE Table #ReplaceCharacters ( Original nvarchar(50))
INSERT INTO #ReplaceCharacters (Original)
SELECT ';#A2;#X4;#' UNION ALL
SELECT ';#A2;#Y9;#X7' UNION ALL
SELECT ';#X2'
SELECT * FROM #ReplaceCharacters
-- Desired results . Add space after comma if there are more than one values
SELECT 'A2, X4' AS Original UNION ALL
SELECT 'A2, Y9, X7' AS Original UNION ALL
SELECT 'X2' AS Original
DROP TABLE #ReplaceCharacters
August 22, 2020 at 4:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
August 22, 2020 at 6:46 pm
Considering that it's exactly 1 hour off, I'm thinking this is some sort of Daylight Saving Time issue.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 23, 2020 at 11:32 am
The task is trivial, but there are no identifiers that can be used to group each SharePoint collection of entries, can you please expand on this?
😎
August 23, 2020 at 6:10 pm
Jeff Moden wrote:Considering that it's exactly 1 hour off, I'm thinking this is some sort of Daylight Saving Time issue.
I'm sorry, Jeff, but I am lost by this comment.
Heh... I don't blame you for that. I posted to the wrong bloody thread. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
August 23, 2020 at 6:24 pm
Hi,
I want to remove ;# coming from SharePoint . The first and last occurrences should be removed and if there are more than one values then I want to replace the middle values with a ,comma and space . Example Q1, Q2, Q3
Thanks,
PSB
CREATE Table #ReplaceCharacters ( Original nvarchar(50))
INSERT INTO #ReplaceCharacters (Original)
SELECT ';#A2;#X4;#' UNION ALL
SELECT ';#A2;#Y9;#X7' UNION ALL
SELECT ';#X2'
SELECT * FROM #ReplaceCharacters
-- Desired results . Add space after comma if there are more than one values
SELECT 'A2, X4' AS Original UNION ALL
SELECT 'A2, Y9, X7' AS Original UNION ALL
SELECT 'X2' AS Original
DROP TABLE #ReplaceCharacters
I don't know enough about SharePoint to speak to the issue that Eirikur brought up but, especially since you're using SQL Server 2017, he's absolutely correct about this being a trivial issue.
Thanks to you posting readily consumable data, you've made it easy on us (thank you for that). Here's the code I believe you're looking for...
SELECT Original
,Cleaned = REPLACE(TRIM(' ;#' FROM Original),';#',', ')
FROM #ReplaceCharacters
;
... and here are the results...
You've just gotta love the TRIM function that they've finally gotten around to adding to SQL Server as of 2017.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 25, 2020 at 11:40 am
Hi,
Thanks for the solution. It works as expected. Just noticed the main table has an extra space at the beginning of each 1st value. How do I change the query to add an extra space at the beginning .
SELECT ' A2, X4' AS Original UNION ALL
SELECT ' A2, Y9, X7' AS Original UNION ALL
SELECT ' X2' AS Original
August 25, 2020 at 1:36 pm
Try this.
SELECT Original
,Cleaned = REPLACE(Original,';#',', ')
FROM #ReplaceCharacters
;
August 25, 2020 at 6:19 pm
Try this.
SELECT Original
,Cleaned = REPLACE(Original,';#',', ')
FROM #ReplaceCharacters
;
Ah, careful now... 😉 It doesn't produce what the OP wanted. Instead, it includes a leading comma on all rows and a trailing comma for the first row.
This is what your code produces using the given test data...
This is what the OP wanted...
--Jeff Moden
Change is inevitable... Change for the better is not.
August 25, 2020 at 7:06 pm
Hi,
Thanks for the solution. It works as expected. Just noticed the main table has an extra space at the beginning of each 1st value. How do I change the query to add an extra space at the beginning .
SELECT ' A2, X4' AS Original UNION ALL
SELECT ' A2, Y9, X7' AS Original UNION ALL
SELECT ' X2' AS Original
If you look at the code I posted as the original solution (repeated here)...
SELECT Original
,Cleaned = REPLACE(TRIM(' ;#' FROM Original),';#',', ')
FROM #ReplaceCharacters
;
... only a small change needs to be made to the "Cleaned" column and that is that the result of the formula I used simply needs to be concatenated to an always-present leading space. There are at least 3 basic different ways to do so this. With the idea of "teaching a man to fish), here they are...
SELECT Original
,Cleaned1 = ' '+REPLACE(TRIM(' ;#' FROM Original),';#',', ')
,Cleaned2 = SPACE(1)+REPLACE(TRIM(' ;#' FROM Original),';#',', ')
,Cleaned3 = CHAR(32)+REPLACE(TRIM(' ;#' FROM Original),';#',', ')
,Cleaned4 = CONCAT(' ',REPLACE(TRIM(' ;#' FROM Original),';#',', '))
,Cleaned5 = STUFF(REPLACE(TRIM(' ;#' FROM Original),';#',', '),1,0,' ')
FROM #ReplaceCharacters
;
... and here are the results... all 5 methods produce the same output for the given data.
Heh... I know... I said 3 different basic methods and then I post 5 different ways. What's up with that?
Method 1 - Basic concatenation operator ("+" when used for strings) using a string literal for the space.
Method 2 - Same as Method 1 but the SPACE() function was used instead of a literal.
Method 3 - Same as Method 1 but the numeric ASCII value of a space character was used (32) and then the CHAR() function was used to convert that to a space. See the ASCII table at http://www.asciitable.com/ for a more complete list of the "7 bit" characters in the world of ASCII (which stands for the American Symbolic Code for Information Interchange). The "8 bit" characters are known as "Extended ASCII" characters and can vary by a huge amount depending on which code page and/or collation settings your system is setup to use.
All 3 of the above methods will result in a NULL if the formula ever evaluates to NULL because NULL concatenated with anything else will result in a NULL
Method 4 - This simply uses the CONCAT() function to concatenate a comma separated list of values. The difference between this and any of the previous methods is that, if the formula results in NULL, the return will be a single space because CONCAT() simply ignores expressions that evaluate as NULL.
Method 5 - This is serious overkill for this simple problem of adding a leading space but it does introduce the STUFF() function, which is incredibly useful in other areas.
I strongly recommend you do a search the "+ concatenation operator", SPACE() function, CHAR() function, CONCAT() function, and STUFF() function and learn much more about them because they have a lot of use in the world of SQL. You might also want to take a look at the new-to-2017 STRING_AGG() function, which also has some incredible use.
Of course, don't forget to look up and study the TRIM() function I used in the original code. 😉
Any questions? 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
August 26, 2020 at 4:32 pm
Thanks. Works as expected.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply