October 9, 2015 at 12:05 pm
I tried posting the hard way to do it but can't from here. So, replace the call to dbo.GetInvoiceIDFromBillID with the following to see what I did, just for S & G's.
right(left(BillID,
case when charindex('-',BillID) > 0
then charindex('-',BillID) - 1
else len(BillID)
end), len(left(BillID,case when charindex('-',BillID) > 0
then charindex('-',BillID) - 1
else len(BillID)
end)) - patindex('%[0-9]%', left(BillID,case when charindex('-',BillID) > 0
then charindex('-',BillID) - 1
else len(BillID)
end)) + 1)
October 9, 2015 at 12:17 pm
Lynn Pettis (10/9/2015)
I tried posting the hard way to do it but can't from here. So, replace the call to dbo.GetInvoiceIDFromBillID with the following to see what I did, just for S & G's.
right(left(BillID,
case when charindex('-',BillID) > 0
then charindex('-',BillID) - 1
else len(BillID)
end), len(left(BillID,case when charindex('-',BillID) > 0
then charindex('-',BillID) - 1
else len(BillID)
end)) - patindex('%[0-9]%', left(BillID,case when charindex('-',BillID) > 0
then charindex('-',BillID) - 1
else len(BillID)
end)) + 1)
^-- This.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);October 9, 2015 at 12:24 pm
mister.magoo (10/9/2015)
Lynn Pettis (10/9/2015)
I tried posting the hard way to do it but can't from here. So, replace the call to dbo.GetInvoiceIDFromBillID with the following to see what I did, just for S & G's.
right(left(BillID,
case when charindex('-',BillID) > 0
then charindex('-',BillID) - 1
else len(BillID)
end), len(left(BillID,case when charindex('-',BillID) > 0
then charindex('-',BillID) - 1
else len(BillID)
end)) - patindex('%[0-9]%', left(BillID,case when charindex('-',BillID) > 0
then charindex('-',BillID) - 1
else len(BillID)
end)) + 1)
^-- This.
Or this?
SUBSTRING( LEFT( BillID, ISNULL( NULLIF( charindex('-',BillID), 0) - 1, 20)), PATINDEX( '%[0-9]%', BillID), 20)
October 9, 2015 at 2:21 pm
Finally got to do some testing.
First and foremost... Getting the UDFs out of the function did allow the C&P column to index and I don't see the function showing up in the execution plan when querying the test table.
So that's huge!
The code posted by Luis & Lynn both worked much faster than the tally table method and all 3 appeared to give the correct results.
For my current version of the function I borrowed a little bit from everybody...
ALTER FUNCTION dbo.GetInvoiceIDFromBillID
/* ===========================================================
10/09/2015 JL, Created to strip the InvoiceID out or the BillID
to be used to create a persisted, computed column on the BlahBlahBlah table
=========================================================== */
(
@BillID VARCHAR(20)
)
RETURNS INT WITH SCHEMABINDING AS
BEGIN
DECLARE @x VARCHAR(20) = SUBSTRING(@BillID, PATINDEX('%[0-9]%', @BillID), 20);
DECLARE @InvoiceNumber INT = (SELECT CAST(SUBSTRING(@x, 1, ISNULL(NULLIF(PATINDEX('%[^0-9]%', @x) -1, -1), 20)) AS INT));
RETURN @InvoiceNumber;
END;
I made one slight change... Since I have no control over the formatting I decided to play it safe and grab the 1st integer string in the BillID. That way when the powers to be decide to throw some other unforeseen format in there, it won't break anything. (It'll return 0 if no numeric values are in the string)
As for speed... It was able to calculate the invoice number for the 2.86 million rows currently in the target table in 12 seconds. I don't think that's too bad considering that I'm stuck using a scalar udf.
A HUGE thank you to everyone who contributed. You help is very much appreciated.
October 9, 2015 at 2:29 pm
Jason A. Long (10/9/2015) I don't think that's too bad considering that I'm stuck using a scalar udf.
On 2nd thought... I'm not stuck with a scalar udf... Just use the function code and get rid of the variables... DUH! :hehe:
This executes in about 3 seconds...
DECLARE
@b-2 VARCHAR(20),
@i INT;
SELECT
@b-2 = sp.BillID,
@i = CAST(SUBSTRING(
SUBSTRING(sp.BillID, PATINDEX('%[0-9]%', sp.BillID), 20),
1, ISNULL(NULLIF(PATINDEX('%[^0-9]%',
SUBSTRING(sp.BillID, PATINDEX('%[0-9]%', sp.BillID), 20)
) -1, -1), 20)) AS INT)
FROM
dbo.BlahBlahBlah_PRI sp
October 9, 2015 at 3:07 pm
The following doesn't use an UDF or CLR, and seems to cover all the (4) scenarios you described above.
declare @Invoice as table( BillID varchar(20) primary key );
insert into @Invoice ( BillID )
values ('A6304158-2'),('A6304158'),('6304158-2'),('6304158');
select BillID
, substring
(
BillID
, patindex('%[0-9]%',BillID)
, len(BillID) - patindex('%[0-9]%',BillID) + 1
- case when patindex('%-%',BillID) > 0
then len(BillID) - patindex('%-%',BillID) + 1
else 0
end
) as NewBillID
from @Invoice;
BillID NewBillID
6304158 6304158
6304158-2 6304158
A6304158 6304158
A6304158-2 6304158
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply