Morning

Morning

@JohnK.N. I wonder if some of Solomon's functions could come in handy.

@JohnK.N. do you want to remove all CRLF or only those at the end of the text?

I want to replace CRLF with @@.

The idea is to replace CRLF with @@ during the export and then on re-import to the new system replace @@ with CRLF.

@JohnK.N. would you accept a CLR solution?

Not sure. The less I have to alter the SQL Server instance, the better. I'm just going to add an important bit of information to the question.

Converting the ntext value to nvarchar(max) is the right answer

@PaulWhite ok

@PaulWhite Does that still work if the ntext column has a record with more than 4000 characters?

yes it does. Just read the answer

@JohnK.N. Of course

TA

how to get the people that you are asking for help annoyed postgresql.org/message-id/…

@ypercubeᵀᴹ perhaps you need to ask more than once for a high precision answer.

o_O

good morning heap

@McNets Depending on the algorithm, it could converge on the correct answer more quickly than direct computation

@bbaird ;) morning

@PaulWhite I just took closer look at what your deterministic RemoveNonNumericCharacters() function for 2017+ is doing, and that's pretty smart. Translating the fixed list of characters you do want, so you can use the output to re-translate out the potentially infinite list of stuff you don't want. 👏 Awesome!

Just curious, why the collation clause?

weird question of the day

As a dumb American, I was intrigued by that question because I read pounds as a unit of weight.

11.3 kg of cash would be a lot to haul around 😅

@PaulWhite In this table swap, is it possible that new rows can go in the old table during the transaction, or is a table lock automatically going to happen due to the drop and rename later in the steps (assuming I wrap the whole thing in a transaction)?

@J.D. It makes string operations a lot faster when binary comparison is all that is needed. The full Unicode comparison rules are powerful but expensive

@CadeRoux Yes it is possible. Locks are taken as needed, not in advance.

So if concurrent inserts to the old table are possible you'd need to account for those before dropping 😀

To state the bleeding obvious at you

Meaning our OLTP system's service while they do the maintenance.

Like if I put WITH (TABLOCK) on the source table in the SELECT is that lock going to stay on the whole transaction or is it going to be released right before the DROP?

Seems like I need to ensure the isolation level is serializable.

Sounds good, thanks Paul!