Mass replace of DB codes
-
Hi,
I have a column in a table that contains variable semi-colon DB codes such as:
;code1;code2;
;code2;
;code1;code3;code4;
A separate table contains the text for each code.
I have a scalar function that converts them to
PlainText1; PlainText2
PlainText2
PlainText1; PlainText3; PlaintText4
However the performance is terrible on large rows.
What query could I write that doesn't use a scalar function?
I have SQL Server 2017.
Thanks
-
Do you need to preserve the order of the codes in any way, shape, or fashion? If not, you can use the String_Split() function, which has been available in SQL Server since the 2016 version.
https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql
If you need to maintain the order or you're not actually using SQL Server 2016 or above, then use the iTVF function (is is NOT a slow scalar function... it a very high performance inline Table Valued Function) at the following link.
As a but if a side bar, anyone that says that any variation of the "XML" splitter is performant are actually out of their minds because every article I've ever seen on it fails to test things correctly. It doesn't even come close. Here's a chart from the article where the various splitter methods actually were tested correctly. The skinny little Black line is the performance curve of the splitter in the article (DelimitedSplit8K). DelimitedSplitN4K (in the same article) is the Unicode variant and it's just as fast.
--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)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply