Just to be safe, I would CAST the text to varchar(max).  And, also, add code in case '</Node1>' is missing:

select
table1.Id, 
substring(
ca1.source_data_varchar, 
charindex('<Node1>',ca1.source_data_varchar) + 7, 
nullif(charindex('</Node1>',ca1.source_data_varchar), 0) -...

I put a space after the embedded forename (because of the wording of the OP: "who put in their middle name or surname with forenames separated by a space".)

Otherwise, I...

You might avoid some false matches by using:

like '%' + p.forenames + ' %'

 

But you should add the NULLability setting to the column.  The default is somewhat obscure so you should always specify NULL or NOT NULL when adding a column.

I've seen issues repeatedly with "SELECT ... INTO <new_table>" causing blocking (on SQL 2016).  When we adjust the code to split the table creation...

/* dummy post to allow me to see my previous post */

I've seen issues repeatedly with "SELECT ... INTO <new_table>" causing blocking (on SQL 2016).  When we adjust the code to split the table creation and the...

I've seen issues repeatedly with "SELECT ... INTO <new_table>" causing blocking (on SQL 2016).  When we adjust the code to split the table creation and the table load...

I've seen issues repeatedly with "SELECT ... INTO <new_table>" causing blocking (on SQL 2016).  When we adjust the code to split the table creation and the table load...

Derrick Smith (9/15/2010)

---

Jeff Moden (9/14/2010)

---

Derrick Smith (9/14/2010)

---

It's the combination of that and creating it in a transaction thats causing the issue here. SQL needs to keep the lock on...

I've seen issues repeatedly with "SELECT ... INTO <new_table>" causing blocking (on SQL 2016).  When we adjust the code to split the table creation and the table load the problems...

ScottPletcher that is not entirely true and that is not what I was talking to. I was looking at the bigger picture or the why are they creating these...

You don't really need to partition the table if you (1) cluster the table by date first and (2) the lookup queries specify a WHERE clause on a date or...

(1) You could use dynamic SQL and change the table name literally in the code before executing it.

(2) Or you could instead use a synonym to point to the active...

SELECT 
    CAST(Time_2 AS date) AS Date,
    CAST(SUM(CASE WHEN DATEPART(HOUR, Time_2) = 1 THEN Unit ELSE 0 END) AS decimal(9, 3))...