Luis Cazares (1/17/2014)

---

dwain.c (1/17/2014)

---

I agree that you've done some very thorough testing here. Excellent work!

I wasn't...

Original Test strings were small (16 chars), the original function was only for 255 although a few of the functions presented have been able to handle far more. So upping...

Some excellent work

DECLARE @TestVar VARCHAR(16);

DECLARE @StartTime DATETIME;

PRINT 'Old Method Scalar Function'

SELECT @StartTime = GETDATE();

SELECT@TestVar = Utility.f_ProperCase(PT.String)

FROM#ProperTest AS PT;

PRINT...

Yes, I'll have to play around with it a little more when I have some time tomorrow.

I must say i hadn't thought about using the splitter to separate the words...

Trying to piece together some test data, some Uppercase letters with some spaces inserted:

IF OBJECT_ID('tempdb..#ProperTest','U') IS NOT NULL

DROP TABLE #ProperTest;

SELECTSTUFF(STUFF(CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+...

dbo.GetNums is as per below, straight from one of Jeff's discussions/articles I believe:

CREATE FUNCTION [dbo].[GetNums]

(

@low AS BIGINT,

@high AS BIGINT

)

RETURNS TABLE WITH SCHEMABINDING

AS

RETURN

WITH

L0 ...

Think I may have figured it out:

CREATE FUNCTION [dbo].[ProperCase]

(

@StrIn VARCHAR(255)

)

RETURNS TABLE WITH SCHEMABINDING

AS

RETURN

SELECTProperCaseString = (

(SELECTCASE

WHEN GN.N = 1 THEN UPPER(SUBSTRING(@StrIn,GN.N,1))

WHEN SUBSTRING(@StrIn,GN.N-1,1) IN (' ','-','''') THEN UPPER(SUBSTRING(@StrIn,GN.N,1))

ELSE LOWER(SUBSTRING(@StrIn,GN.N,1))

END AS [text()]

FROMdbo.GetNums(1,ISNULL(LEN(@StrIn),1)) AS...

I'd like to point out this is a SQL 2008 forum, but the following should work with the same caveats as before. No 2000 environment to test on though.

DECLARE @Invoice...

Although, question would be what would happen if you have 3 or more item No on one invoice.

I'd have to ask why you would need it in this format, but i think the below should get what you want

DECLARE @Invoice TABLE

(

invoic_noVARCHAR(25),

item_noCHAR(3),

invoic_qtyINT,

inv_dateDATETIME

)

INSERT INTO @Invoice (invoic_no,item_no,invoic_qty,inv_date)

VALUES('tn/0002/13-14','A12',25,'03-04-2013'),

('tn/0002/13-14','A13',04,'03-04-2013'),

('tn/0004/13-14','A13',25,'03-04-2013'),

('tn/0004/13-14','A15',04,'03-04-2013'),

('tn/0005/13-14','A16',07,'03-04-2013'),

('tn/0005/13-14','A17',04,'03-04-2013')

SELECTI.invoic_no,

I.item_no,

--MAX(I.invoic_qty) OVER (PARTITION BY...

This sounds like a 'catch-all' query and can be achieved with dynamic SQL.

Have a look at the below for an excellent description and examples:

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Following the theme of the article, I don't claim the idea of this to be mine, but I've found OVER functions are very handy for calculating median times.

DECLARE

You're passing a string as an argument rather than the column name. Remove the Apostophes i.e. CustomerName instead of 'CustomerName'

Thanks Raunak, I've only had time to quickly flick through this, but I think this may go some way to achieving...

I believe the algorithm for calculating the check digit takes the difference between the 'weighted sum' and the nearest multiple of 10 that is equal or higher.

if the sum was...