I think you might want a recursive common table expression.

Something like this:

DECLARE @n int = 3;
WITH Factorial(F,n) AS 
(
    SELECT 1  ...

Heh... lordy. I can't believe how long I've been at this...

I've come up with nothing in my searches, as well.  Hopefully, someone will see this bit of a bump and know.

Ok.  Here's an iTVF that will do the trick for you in which ever way you might want to solve your problem.  As with most things, the details are in...

It would appear that a simple definition of what you want is as follows:

Return the last weekday of the month (regardless of any holidays).

To help others understand that,...

Nice.... you don't even need the CTE with that method.

SELECT
Customername
,PATINDEX('%[ ][a-zA-Z]%', c.CustomerName) - PATINDEX('%[a-zA-z][ ]%', c.CustomerName) CountSpaces
FROM #CustomerData c

Just curious - but if you had to transfer PII/PCI/PHI for a conversion (for example) where the unencrypted data is required by the receiving party, how would...

In Microsoft SQL, you can find the spaces between the first and last name using various string manipulation functions. One approach is to use the CHARINDEX function, which returns...

Nope.  Not making any assumptions here.  I know and stated exactly what I'd do.  People that do otherwise...

Also, never use SET STATSTICS if the code involves ANY Scalar UDF.  It'll make the code look a serious amount slower than it actually is.  Please see the following article...

This will make some people get all scratchy but I use SQL Profiler... usually with a SPID filter.

Just...

• This reply was modified 2 years, 6 months ago by Jeff Moden.
• This reply was modified 2 years, 6 months ago by Jeff Moden.

Heh... you're seriously going to export SSNs to a file in an unencrypted manner along with all that nice PII.  What company did you say you work for?

One comment: I hope the example table isn't an exact replica of your production table, because to use that many columns defined as nvarchar(max) just to be on the...

You just got done saying "I tried your suggestion and I have...