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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy