February 13, 2014 at 3:19 am
I have a function dbo.fn_ConvertToNumeric(@a) and am using it in an update statement for a table having millions of records.
update tbl_source
set Col1 = case when col2 = 'abc'
then dbo.fn_ConvertToNumeric(col3) end
Col3 may or may not have values suffix (like -xyz, -abc etc) which is getting removed by the function.
Now i have to add one more logic where when col2 = 'abc' then case when len(col3) > 10 and col3 like '111%' then i've to pick substring(col3, 4, len(col3)) end else dbo.fn_ConvertToNumeric(col3) end.
I want to write it in efficient way so that my overall process does not have to bear cost of one additional update.
February 13, 2014 at 6:12 am
If I understand you....
UPDATE tbl_source
SET Col1 = CASE WHEN LEN(col3) > 10 AND col3 LIKE '111%'
THEN SUBSTRING(col3, 4, LEN(col3) ELSE dbo.fn_ConvertToNumeric(col3) END
WHERE col2 = 'abc';
using a WHERE clause to reduce the number of updates.
February 13, 2014 at 7:52 am
This is simplest one and i tried it by my own. It will not consider the values like
col3 = 11123456789-xyz
In this case it should be 23456789
February 13, 2014 at 7:56 am
What i'm looking for is any update like this:
UPDATE tbl_source
SET Col1 = CASE WHEN LEN(dbo.fn_ConvertToNumeric(col3)) > 10 AND dbo.fn_ConvertToNumeric(col3) LIKE '111%' THEN SUBSTRING(dbo.fn_ConvertToNumeric(col3), 4, LEN(dbo.fn_ConvertToNumeric(col3)) ELSE dbo.fn_ConvertToNumeric(col3) END
WHERE col2 = 'abc';
But it doesn't feel good to me calling same function five times in the update.
February 13, 2014 at 8:19 am
If you're updating all those rows, you should consider changing your scalar function to an inLine table-valued function. It will boost your performance.
If you need help on how to do it, post sample data, rules and expected results for the function.
February 13, 2014 at 1:37 pm
If the function just removes an optional trailing '-' followed by other chars, you can avoid a function completely using CROSS APPLY:
UPDATE ts
SET
Col1 = CASE WHEN LEN(col3_num) > 10 AND col3_num LIKE '111%'
THEN SUBSTRING(col3_num, 4, 20)
ELSE col3_num END
FROM tbl_source ts
CROSS APPLY (
SELECT LEFT(col3, CHARINDEX('-', col3 + '-') - 1) AS col3_num
) AS cross_apply_1
WHERE
Col2 = 'abc'
Note that this code is not identical to your original code. Your original code would have updated every row, this code only updates rows where Col2 = 'abc'.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 13, 2014 at 10:14 pm
ScottPletcher (2/13/2014)
If the function just removes an optional trailing '-' followed by other chars, you can avoid a function completely using CROSS APPLY:
UPDATE ts
SET
Col1 = CASE WHEN LEN(col3_num) > 10 AND col3_num LIKE '111%'
THEN SUBSTRING(col3_num, 4, 20)
ELSE col3_num END
FROM tbl_source ts
CROSS APPLY (
SELECT LEFT(col3, CHARINDEX('-', col3 + '-') - 1) AS col3_num
) AS cross_apply_1
WHERE
Col2 = 'abc'
Note that this code is not identical to your original code. Your original code would have updated every row, this code only updates rows where Col2 = 'abc'.
Thanks Scott. But my data in col3 mostly contains numeric but may contain very less amount of alphanumeric characters as well. Something goes like this:
Col3
123454321
12345678-ABC
10123456789
10123456789-A
11199999999
11122222222-X
In this case the function gives value as
Col3
123454321
12345678
10123456789
11199999999
11122222222
Result i want is
Col3
123454321
12345678
10123456789
99999999 -- removed 111
22222222 -- removed 111
February 14, 2014 at 9:08 am
I can't find the problem you mention. Maybe the sample data is wrong.
WITH tbl_source AS(
SELECT Col2, Col3
FROM (VALUES('abc','123454321'),
('abc','12345678-ABC'),
('abc','10123456789'),
('abc','10123456789-A'),
('abc','11199999999'),
('abc','11122222222-X'))x(Col2, Col3)
)
SELECT Col1 = CASE WHEN LEN(col3_num) > 10 AND col3_num LIKE '111%'
THEN SUBSTRING(col3_num, 4, 20)
ELSE col3_num END
FROM tbl_source ts
CROSS APPLY (
SELECT LEFT(col3, CHARINDEX('-', col3 + '-') - 1) AS col3_num
) AS cross_apply_1
WHERE
Col2 = 'abc'
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply