Viewing 15 posts - 106 through 120 (of 7,613 total)
Good point. I was in too much of a hurry when I wrote the other code:
DECLARE @x varchar(500) = 'xxx. #1234 has been replaced by #014521...
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".
October 23, 2024 at 10:11 pm
An alternative (maybe slightly less overhead?, esp. for long strings):
SELECT LEFT(item, CHARINDEX(' ', item + ' ')) AS value
FROM dbo.DelimitedSplit8K (@x, '#') /*or STRING_SPLIT(), if available to...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".
October 23, 2024 at 6:03 pm
I suggest removing ALL non-numeric values in the query itself rather than trying to go thru the data. For example, by making this mod to the query:
...
...
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".
October 21, 2024 at 7:42 pm
No DDL provided, but my best guess is that one of these columns:
t1.invoicenum; t2.invoicenum
is integer and the other is not, and that one contains a value of 'VZ34-031'
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".
October 18, 2024 at 6:19 pm
Since they are "or" conditions, SQL should be able to stop evaluating at the "first" one that is true. In theory, though, SQL could re-arrange the checks and make a...
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".
October 18, 2024 at 1:33 pm
I think this will match what you need, if I understand correctly:
SELECT ...
FROM dbo.baseTable bt
LEFT OUTER JOIN dbo.sentTable st ON st.sent_rowid = bt.rowid
WHERE ((@vendor IS NULL AND st.sent_rowid IS NULL)...
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".
October 16, 2024 at 5:35 pm
If you only want to replace leading and/or trailing commas, then this:
UPDATE tn
SET oppo_SCRMcompetitor = CASE WHEN LEFT(oppo_SCRMcompetitor_ca1, 1) = ','
THEN STUFF(oppo_SCRMcompetitor_ca1,...
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".
October 15, 2024 at 6:50 pm
If you always want to replace every comma, you can do this:
UPDATE dbo.table_name
SET oppo_SCRMcompetitor = REPLACE(oppo_SCRMcompetitor, ',', '')
WHERE oppo_SCRMcompetitor LIKE '%,%'
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".
October 15, 2024 at 6:46 pm
That is SQL's method for handling insufficient space on a CAST.
The only safe method would be to use 11 chars. 10 max digits plus the prefix char (assuming you don't...
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".
October 15, 2024 at 4:20 pm
Definitely focus on the clustered index, first and foremost!
The best way to choose the keys is to look at the missing index stats and current index usage stats and decide...
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".
October 15, 2024 at 1:57 pm
If you just want to change the result column in the SELECT, then do this:
...,
CustID =
(CASE
WHEN custid like '%abc%' and company = 'abc'
then null
ELSE custid
END),
...
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".
October 8, 2024 at 7:47 pm
As to performance:
I figure SQL would have to partition the data twice to get the COUNT() and the AVG() from different queries, but I'd need to look at the query...
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".
October 8, 2024 at 6:35 pm
It's "> 2" :-).
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".
October 8, 2024 at 6:25 pm
I'm not sure how we'd be able to determine whether more than 2 rows are present with that style of query, in order to meet the stated requirements. Then again,...
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".
October 8, 2024 at 5:30 pm
My primary goal is to meet the stated business requirements for the code, which I don't believe your code does. You will list the "truePct" for even a single row,...
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".
October 8, 2024 at 5:15 pm
Viewing 15 posts - 106 through 120 (of 7,613 total)