Replace cpecial characters from SharePoint
-
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 -
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
Considering that it's exactly 1 hour off, I'm thinking this is some sort of Daylight Saving Time issue.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - 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.
-
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?
😎
- Lynn Pettis wrote: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
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - PSB wrote:
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 #ReplaceCharactersI 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
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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 -
Try this.
SELECT Original
,Cleaned = REPLACE(Original,';#',', ')
FROM #ReplaceCharacters
; - Brahmanand Shukla wrote:
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
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - PSB wrote:
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 OriginalIf 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.
- The first operand is the function (string to be worked with).
- The second operand is the character position in the first operand that we want the STUFF() function to start working at. In this case, we want to start at the beginning of the string and so we use the value of "1".
- The third operand is how many characters we want to replace in the first operand starting at the position indicated by the second operand. In this case, we don't want to replace any characters and, instead, want it to be an insertion.
- The fourth operand is the string we want to either insert at the position of the 2nd operand when the 3rd = 0 or the string we want to use to replace the number of characters starting at the 1st operand for the number of characters identified by the 2nd operand.
- STUFF() will also return a NULL if the formula returns a NULL.
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
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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